Sql-server – Get All data from one table according to another, using Temporal Tables

sql servertemporal-tables

I have two simple tables:

CREATE TABLE [dbo].[StatusForStudents](
    [ID] [int] NOT NULL,
    [PrimaryStatusID] [int] NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE [dbo].[Students](
    [ID] [int]  NOT NULL,
    [IsFoster] [bit] NOT NULL,
    PRIMARY KEY (ID)
);

With Temporal Tables feature working. Each have a ValidFrom and ValidTo fields.

I've added some dummy data:

insert into Students values (1,1)
WAITFOR DELAY '00:00:01';
insert into statusforstudents values (1,1)
WAITFOR DELAY '00:00:01';
insert into statusforstudents values (2,2)
WAITFOR DELAY '00:00:01';
insert into Students values (2,2)
WAITFOR DELAY '00:00:01';
insert into statusforstudents values (3,3)

Which got me to:

statusforstudents 
ValidFrom               ID  PrimaryStatusID
2020-12-14 13:18:44.25  1   1
2020-12-14 13:18:45.27  2   2
2020-12-14 13:18:47.30  3   3   


students 
ValidFrom               ID  IsFoster
2020-12-14 13:18:43.22  1   True
2020-12-14 13:18:46.28  2   True

I now want for each record of statusforstudents, to match the relevant record of students.
That is – for each record in statusforstudents, find the record of students that was valid at the statusforstudents's time.

Or, in my example (ss for statusforstudents, s for students) the expected result would be:

ss.ValidFrom            ss.ID   ss.PrimaryStatusID     s.ValidFrom              s.ID  s.IsFoster
2020-12-14 13:18:44.25  1       1                      2020-12-14 13:18:43.22   1     True
2020-12-14 13:18:45.27  2       2                      2020-12-14 13:18:43.22   1     True
2020-12-14 13:18:47.30  3       3                      2020-12-14 13:18:46.28   2     True

I couldn't come up with anything better than just joining them, but that didn't give me anything useful..

Here's a working example

Best Answer

Is this what you are looking for?

SELECT ss.validfrom AS 'status validFrom', ss.ID, ss.PrimaryStatusID, ca.[students validFrom], ca.ID, ca.IsFoster 
FROM StatusForStudents ss
CROSS APPLY
(SELECT TOP(1) s.validfrom AS 'students validFrom', s.ID,s.IsFoster
FROM Students s 
WHERE s.validfrom<=ss.validfrom
ORDER BY s.validfrom DESC
) AS ca
Related Question