Syntax Weirdness with FOR SYSTEM_TIME AS OF [date]

• Sonny Childs

  SSC-Addicted

  Points: 411

  More actions

  May 11, 2017 at 9:51 am

  #402052

  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.

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  May 11, 2017 at 10:10 am

  #1942189

  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 absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  May 11, 2017 at 11:15 am

  #1942197

  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

• Sonny Childs

  SSC-Addicted

  Points: 411

  More actions

  May 11, 2017 at 11:17 am

  #1942198

  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.

• Sonny Childs

  SSC-Addicted

  Points: 411

  More actions

  May 11, 2017 at 11:19 am

  #1942200

  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?

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  May 11, 2017 at 11:45 am

  #1942206

  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!

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Razvan Socol

  SSCarpal Tunnel

  Points: 4811

  More actions

  September 10, 2018 at 1:05 am

  #2005015

  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


• andycadley

  SSCertifiable

  Points: 5296

  More actions

  September 10, 2018 at 4:42 pm

  #2005164

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