How to join two temporal tables using "for system_time" to see historic changes across two tables?

How to join two SQL Server temporal tables using "for system_time" to see historic changes across two tables?

That is say tables: People, Pets within SQL Server each a temporal table. For example:

People: ID, Name, Address, ValidFrom, ValidTo
Pets: ID, PeopleID, Name, ValidFrom, ValidTo

Using "for system_time" (to include current and history) how does one "join" across these tables to see total joined history? That is for each row (current & history) from People, what was the pet(s) that were with them at this time.

By for each row I mean: "Select * from People for system_time all". To get rows from Pets is possible using "select * from Pets AS OF <date_time>" I understand, however, how do one create the people select table, and then include columns in the join from the Pets table using the "as of <date_time>" concept from SQL Server temporal tables.

Result may look like:

Bob, Address 1, Rover
Bob, Address 2, Rover
Bob, Address 2, Rusty
Bob, Address 3, Rusty

Solution

You must manually join intersected People and Pets periods.

Create tables:

CREATE TABLE dbo.People (
    Id        INT                                     PRIMARY KEY,
    Name      VARCHAR (100)                          ,
    Address   VARCHAR (100)                          ,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END  ,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.PeopleHistory));

CREATE TABLE dbo.Pets (
    Id        INT                                     PRIMARY KEY,
    PeopleId  INT                                     CONSTRAINT FK_Pets_People FOREIGN KEY REFERENCES dbo.People (Id),
    Name      VARCHAR (100)                          ,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END  ,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.PetsHistory));

Fill data:

INSERT  INTO dbo.People (Id, Name, Address)
VALUES                 (1, 'Bob', 'Address 1');

INSERT  INTO dbo.Pets (Id, PeopleId, Name)
VALUES               (1, 1, 'Rover');

WAITFOR DELAY '00:00:01';

UPDATE dbo.People
SET    Address = 'Address 2'
WHERE  Id = 1;

WAITFOR DELAY '00:00:01';

UPDATE dbo.Pets
SET    Name = 'Rusty'
WHERE  Id = 1;

WAITFOR DELAY '00:00:01';

UPDATE dbo.People
SET    Address = 'Address 3'
WHERE  Id = 1;

Query history:

SELECT   p.Name, p.Address, pt.Name PetName, p.ValidFrom
FROM     dbo.People FOR SYSTEM_TIME ALL AS p
         INNER JOIN
         dbo.Pets FOR SYSTEM_TIME ALL AS pt
         ON pt.PeopleId = p.Id
            AND pt.ValidFrom BETWEEN p.ValidFrom AND p.ValidTo
UNION
SELECT   p.Name, p.Address, pt.Name PetName, p.ValidFrom
FROM     dbo.People FOR SYSTEM_TIME ALL AS p
         INNER JOIN
         dbo.Pets FOR SYSTEM_TIME ALL AS pt
         ON pt.PeopleId = p.Id
            AND p.ValidFrom BETWEEN pt.ValidFrom AND pt.ValidTo

ORDER BY p.ValidFrom;

The best benefit of temporal table is that you can create a difficult view and query result for particular time point without any additional effort:

CREATE VIEW dbo.vPeoplePets
AS
(SELECT pp.Name,
        pp.Address,
        pt.Name AS PetName
 FROM   dbo.People AS pp
        INNER JOIN
        dbo.Pets AS pt
        ON pt.PeopleId = pp.Id);

SELECT *
FROM   vPeoplePets FOR SYSTEM_TIME AS OF '2023-11-30 12:31:11';

Result is one line for that time point

Name    Address PetName
Bob Address 2   Rusty