Temporal Tables - "Parameterise" extended SYSTEM_TIME syntax

Question

    SELECT *
    FROM Spine    S
    CROSS
    APPLY
    (
        SELECT *
        FROM PersonTemporalTable
        FOR SYSTEM_TIME AS OF S.DateTimeValueOfInterest
    )            CA

It would be much simpler if I left out the "why" of it and stuck to the "what" and "how". I need to be be able to query a Temporal table for record state at specific points in time - were the syntax above legal, it would do precisely what I am after. In this case, the table Spine contains a list of DATETIME values of interest.

Is this simply a matter of not using the correct syntax or is it a limitation? Besides going with dynamic SQL, do you guys have any other suggestions I could try?

Thanks in advance!

---

This is what I landed up doing, based on suggestions by @SQLZim

SELECT *
FROM Spine      SP
JOIN PetTemporal    FOR SYSTEM_TIME ALL PT  ON  SP.SpineDT >= PT.ValidFrom AND SP.SpineDT < PT.ValidTo
JOIN PersonTemporal FOR SYSTEM_TIME ALL PS  ON  SP.SpineDT >= PS.ValidFrom AND SP.SpineDT < PS.ValidTo
                                            AND PS.PersonID = PT.PersonID

Answer 1

If you want to return Spine rows even if there is no corresponding row in PersonTemporalTable, switch to outer apply().

If PersonTemporalTable contains an effective date range:

select *
from Spine S
  cross apply (
    select ptt.*
    from PersonTemporalTable
    where ptt.FromDateTime <= S.DateTimeValueOfInterest
      and ptt.ThruDateTime >= S.DateTimeValueOfInterest
  ) CA

If PersonTemporalTable contains a single effective date:

select *
from Spine S
  cross apply (
    select top 1 ptt.*
    from PersonTemporalTable ptt
    where ptt.EffectiveDate <= S.DateTimeValueOfInterest
  order by ptt.EffectiveDate desc
  ) CA

---

If this is supposed to return rows where the two tables share a corresponding key, e.g. PersonId, then include that in the where of the cross apply(), e.g.:

select *
from Spine S
  cross apply (
    select top 1 ptt.*
    from PersonTemporalTable ptt
    where ptt.EffectiveDate <= S.DateTimeValueOfInterest
      and ptt.PersonId = S.PersonId
  order by ptt.EffectiveDate desc
  ) CA

---

Another option that takes advantage of the extended system_time as of syntax: you could create an inline table-valued function to use with cross apply() like so:

create function dbo.PersonTemporalTable_AsOf (@fromdate datetime2(7)) returns table as return (
select *
  from PersonTemporalTable
  for system_time as of @fromdate
)
go
select *
from spine s
  cross apply dbo.PersonTemporalTable_AsOf(s.DateTimeValueOfInterest) ca

dbfiddle.uk demo: http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=f1ee20893fe987e71cbd0cb12d09fccb