Querying temporal table with date/time fields

Question

I need to write a query to get following output using temporal table feature in SQL server 2016.

Customer Table (system versioned table)

Id
Name
[period_start] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL
[period_end] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL

Orders Table

Id
CustomerId
..
[creation_date] DATETIME default SYSUTCDATETIME()

Output : OrderId, ... , Customer Name

Customer name should be the reflect the correct name which relevant to the order date

Following query will give me the required output but I need to check whether this is the correct way of doing this type of queries..

SELECT T.Id, O.Name
FROM Orders T
INNER JOIN (
SELECT *
FROM Customer 
FOR SYSTEM_TIME FROM '1900-01-01' TO '9999-12-31 23:59:59.9999999'
) O ON T.CustomerId = O.ID 
AND T.creation_date BETWEEN O.period_start AND O.period_end

Thanks in advance

Chaminda.

Answer 1

I see nothing wrong with it, except perhaps the nested query is unnecessary:

SELECT T.Id, O.Name
FROM Orders T
JOIN Customer FOR SYSTEM_TIME FROM '1900-01-01' TO '9999-12-31 23:59:59.9999999' O 
    ON T.CustomerId = O.ID 
WHERE T.creation_date BETWEEN O.period_start AND O.period_end