Syntax Weirdness with FOR SYSTEM_TIME AS OF [date]

  • I'm basically trying to get Day-On-Day aggregates of some temporal tables I'm working with.

    Basic task #1 is to get counts for each day. I've decided to use a date dimension table for this, hoping that it would make things easier.

    Here's what I would assume is the obvious approach:
    SELECT 
    dd.[Date],
    (SELECT COUNT(*) FROM computers FOR SYSTEM_TIME AS OF dd.[Date]) as 'ComputerCount'
    FROM DateDimension dd

    However, SQL Server doesn't care for this code. Throws an "Incorrect syntax near 'dd'." message at me.

    Investigating this led me to create a distilled query set of what works and what doesn't with "SYSTEM_TIME AS OF":
    SELECT COUNT(*) FROM computers FOR SYSTEM_TIME AS OF '2017-05-06' -- works fine
    SELECT COUNT(*) FROM computers FOR SYSTEM_TIME AS OF ('2017-05-06') -- syntax error

    DECLARE @muhDate datetime = '2017-05-06'
    SELECT COUNT(*) FROM computers FOR SYSTEM_TIME AS OF @muhDate -- also works fine!!!

    Any thoughts? I've previously posted a similar topic which lead to dynamic SQL generation (which actually works), but I was thinking that this would be more sane...except for the fact that SQL doesn't seem to let anything other than a pre-built variable/string into that part of the syntax.

    I'm basically trying to step up my day-on-day game without continuing to create dynamic UNION ALL queries for each day.

    I can provide more information if necessary.

  • I suspect that dd is a reserved word. Try using [dd] instead. I just tried this with no problem, and it's basically the same construct as yours:
    SELECT
      eh.*
    ,  Nonsense =
      (
       SELECT COUNT(*)
       FROM dbo.Emp
         FOR SYSTEM_TIME AS OF '20150101'
      )
    FROM dbo.EmpHistory eh;


  • The values for the system time can only be literals or variables.  They cannot be date/time fields.

    FROM (Transact SQL)


    <system_time> ::=
    {
       AS OF <date_time>
      | FROM <start_date_time> TO <end_date_time>
      | BETWEEN <start_date_time> AND <end_date_time>
      | CONTAINED IN (<start_date_time> , <end_date_time>
      | ALL
    }

      <date_time>::=
       <date_time_literal> | @date_time_variable

      <start_date_time>::=
       <date_time_literal> | @date_time_variable

      <end_date_time>::=
       <date_time_literal> | @date_time_variable
     

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Phil, I believe the key here is what you're allowed to put after the "AS OF".
    "dd" is not a reserved word, as far as I know, and wrapping it in brackets doesn't change anything.

    Of course the query you wrote is going to work; similar to my example, using a hard-coded data after "AS OF" is perfectly fine, but apparently using a  SELECT'd date is not valid. I'm hoping there's a workaround for this.

    For a test, try doing this:

    SELECT
     eh.*
    , Nonsense =
     (
     SELECT COUNT(*)
     FROM dbo.Emp
      FOR SYSTEM_TIME AS OF ('2015' + '0101')
     )
    FROM dbo.EmpHistory eh;

    or even just:

    SELECT
     eh.*
    , Nonsense =
     (
     SELECT COUNT(*)
     FROM dbo.Emp
      FOR SYSTEM_TIME AS OF ('20150101')
     )
    FROM dbo.EmpHistory eh;

    Maybe this helps to explain what I'm talking about.

    It seems you can't pass anything as an expression to the "AS OF" syntax.

  • drew.allen - Thursday, May 11, 2017 11:15 AM

    The values for the system time can only be literals or variables.  They cannot be date/time fields.

    FROM (Transact SQL)


    <system_time> ::=
    {
       AS OF <date_time>
      | FROM <start_date_time> TO <end_date_time>
      | BETWEEN <start_date_time> AND <end_date_time>
      | CONTAINED IN (<start_date_time> , <end_date_time>
      | ALL
    }

      <date_time>::=
       <date_time_literal> | @date_time_variable

      <start_date_time>::=
       <date_time_literal> | @date_time_variable

      <end_date_time>::=
       <date_time_literal> | @date_time_variable
     

    Drew

    Thanks, Drew.

    This is the documentation I was looking for to confirm my suspicions. Didn't think to look in the FROM docs.

    Any idea how I can work around this requirement outside of my current method of generating hundreds of UNION ALL queries?

  • Ah, sorry, I should have read your post more closely.
    At least it gave me the opportunity to create my first temporal table, so thanks for that!


  • Indeed, the syntax of the FOR SYSTEM_TIME AS OF clause allows only a constant or a variable, not a column.
    To overcome this limitation, you can use a table-valued function to return the data from the temporary table at the time of the specified parameter, for example:


    CREATE FUNCTION GetComputersCount(@DateTimeUTC datetime2(7))
    RETURNS TABLE AS RETURN
    SELECT COUNT(*) AS ComputerCount FROM computers FOR SYSTEM_TIME AS OF @DateTimeUTC
    GO
    SELECT dd.[Date],
    (SELECT ComputerCount FROM dbo.GetComputersCount(dd.[Date])) as 'ComputerCount'
    FROM DateDimension dd

  • I'd probably use FOR SYSTEM_TIME BETWEEN xxx AND yyy and then JOIN that on your dimension table.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply