'Joining SQL Temporal tables as-of time

I'm attempting to do the following:

SELECT report.*,
doc.*
FROM report
FOR system_time ALL report
JOIN Document
FOR system_time as of <<report.BeginDate>> doc ON report.DocumentId = doc.DocumentId

Basically I'd like to get all the history of the parent table, and it's associated childrens table as of the correct time according to the parents row. Is this possible?

sqlsql-servertemporal-tables


Solution 1:[1]

Unfortunately, FOR SYSTEM_TIME only allows a literal or a variable/parameter. It cannot be a column from the query. But you can put this into a inline Table Valued Function and then APPLY the function:

CREATE OR ALTER FUNCTION dbo.DocumentAsOfDate (@documentId int, @asOfDate datetime(7))
RETURNS TABLE
AS RETURN
SELECT d.*
FROM dbo.Document FOR SYSTEM_TIME AS OF @asOfDate AS d
WHERE d.DocumentId = @documentId;

GO

SELECT
  report.*,
  doc.*
FROM report FOR SYSTEM_TIME ALL report
CROSS APPLY dbo.DocumentAsOfDate (report.DocumentId, report.BeginDate) doc;

You may want to consider which date to use (start or end), and whether to use FOR SYSTEM_TIME BETWEEN... or other filter.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Charlieface

Related Questions