Joining SQL Temporal tables as-of time

Question

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?

Answer 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.