Get All data from one table according to another, using Temporal 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
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&lt;=ss.validfrom
ORDER BY s.validfrom DESC
) AS ca