How to reference System Versioned table in a sub query-CodePudding

I have a system versioned temporal table of Contacts. I also have a table of events when contacts where used. I want to aggregate and get a count of the contacts that were available in a list when the event happened.

To do this I am using a sub-query. But when I tried to specify the respective event time SQL Server Management Studio won't compile the query.

How to I reference a System Versioned table in a subquery with a non-hardcoded timestamp?

DECLARE @CurrentTime DATETIMEOFFSET = GETUTCDATE()
DECLARE @LastSentTime DATETIMEOFFSET = DATEADD(day, -28, @CurrentTime)

SELECT
    c.ContactListID,
    ( SELECT COUNT( DISTINCT con.Email )
      FROM [schema].[Contact] FOR SYSTEM_TIME AS OF c.StartDate AS con
      WHERE c.ContactListID = con.ContactListID) as [ContactCount],
    c.StartDate
INTO #TEMP
FROM [schema].[event] c
WHERE StatusID = 3
AND StartDate > @LastSentTime
AND StartDate <= @CurrentTime
ORDER BY StartDate ASC

FOR SYSTEM_TIME AS OF c.StartDate AS con Doesn't work, citing incorrect syntax

But if I hardcode the date string

FOR SYSTEM_TIME AS OF '2022-01-03 23:00:00.00 00:00' AS con

it does

CodePudding user response：

Looking at the documentation, the argument for AS OF must either be a literal or a variable; it doesn't support using a value from a column in an outer query.

FROM clause plus JOIN, APPLY, PIVOT (T-SQL) - SQL Server | Microsoft Docs

<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

You'll need to use a scalar-valued function to count the history rows:

CREATE OR ALTER FUNCTION [schema].CountEmailContacts
(
    @ContactListID int,
    @AsOf datetime2(0)
)
Returns int
As
BEGIN
    Return (SELECT COUNT(DISTINCT Email)
    FROM [schema].[Contact]
    FOR SYSTEM_TIME AS OF @AsOf
    WHERE ContactListID = @ContactListID);
END;

Usage:

SELECT
    c.ContactListID,
    [schema].CountEmailContacts(c.ContactListID, c.StartDate) As [ContactCount],
    c.StartDate
INTO #TEMP
FROM [schema].[event] c
WHERE StatusID = 3
AND StartDate > @LastSentTime
AND StartDate <= @CurrentTime
ORDER BY StartDate ASC

Edit:
Or, as Stu pointed out, an inline table-valued function, which should have better performance:

CREATE OR ALTER FUNCTION [schema].CountEmailContacts
(
    @ContactListID int,
    @AsOf datetime2(0)
)
Returns Table
As
Return 
(
    SELECT COUNT(DISTINCT Email) As ContactCount
    FROM [schema].[Contact]
    FOR SYSTEM_TIME AS OF @AsOf
    WHERE ContactListID = @ContactListID
);

Usage:

SELECT
    c.ContactListID,
    con.[ContactCount],
    c.StartDate
INTO #TEMP
FROM [schema].[event] c
CROSS APPLY [schema].CountEmailContacts(c.ContactListID, c.StartDate) con
WHERE StatusID = 3
AND StartDate > @LastSentTime
AND StartDate <= @CurrentTime
ORDER BY StartDate ASC