Sql-server – JOIN FOR SYSTEM TIME with column as the effective system time

sql serversql-server-2016temporal-tables

Imagine I have a schema that includes Products, Orders, and OrderLineItems, with Products being a system-versioned temporal table.

Schema:

CREATE TABLE dbo.Products
(
    ProductID INT NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(255) not null,
    SysStart DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    SysEnd DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.Products_History, DATA_CONSISTENCY_CHECK = ON));
GO

CREATE TABLE dbo.Orders
(
    OrderID int not null identity primary key,
    OrderDate datetime2 (7) not null
);

CREATE TABLE dbo.OrderLineItems
(
    OrderID int not null,
    ProductID int not null,
    CONSTRAINT FK_OrderLineItems_Orders FOREIGN KEY (OrderID) REFERENCES dbo.Orders (OrderID),
    CONSTRAINT FK_OrderLineItems_Products FOREIGN KEY (ProductID) REFERENCES dbo.Products (ProductID),
    CONSTRAINT PK_OrderLineItems PRIMARY KEY (OrderID, ProductID)
);
GO

-- Load Sample data
insert into Products (Name) values ('a'), ('b');
waitfor delay '00:00:02';
insert into orders (OrderDate) values (getutcdate());
waitfor delay '00:00:02';
update products set name= 'c' where name = 'a'
waitfor delay '00:00:02';
insert into orders (OrderDate) values (getutcdate());
insert into OrderLineItems (OrderID, ProductID) values (1, 1), (1, 2), (2, 1);

How would I write a query which joins Products to OrderLineItems using the date from Orders?

For example, to query "Orders which contains products whose names have changed":

-- Fake syntax:
SELECT o.OrderID, p_then.Name as [Old Name], p_now.Name as [New Name]
FROM dbo.Orders o
INNER JOIN dbo.OrderLineItems oi on o.OrderID = oi.OrderID
INNER JOIN dbo.Products as p_then
    for system time o.OrderDate
    on oi.ProductID = p_then.ProductID
INNER JOIN dbo.Products p_now
--  for system time now
    on oi.ProductID = p_now.ProductID
WHERE p_then.Name <> p_now.Name

Best Answer

You can retrieve all historical rows from a temporal table using FOR SYSTEM_DATE ALL. That allows a manual comparison of the OrderDate to the SysStart and SysEnd dates:

SELECT o.OrderID, p_then.Name as [Old Name], p_now.Name as [New Name]
FROM dbo.Orders o
INNER JOIN dbo.OrderLineItems oi on o.OrderID = oi.OrderID
INNER JOIN dbo.Products FOR SYSTEM_TIME ALL as p_then
    on o.OrderDate >= p_then.SysStart and o.OrderDate < p_then.SysEnd
    and oi.ProductID = p_then.ProductID
INNER JOIN dbo.Products p_now
    on oi.ProductID = p_now.ProductID
WHERE p_then.Name <> p_now.Name
Related Question