Include original data with current data from temporal tables in a view?

Question

I am creating a view based on a system-versioned table that records account information. The primary key is an automatically-incremented "Account No". My period columns are "Valid From" and "Valid To". I also assign a new "Version No" each time the data changes.

Within this view, I would like to use the "Valid From" value from the most recent version of each account to represent the account's "Time Modified." Then, I would like to use the "Valid From" value from the oldest version of each account (i.e., where "Version No" = 1) to represent the account's "Time Created."

How should I do this?

Answer 1

While it is possible to use FOR SYSTEM_TIME ALL and aggregation, it's probably easiest (and fastest) to just join the history table directly.

SELECT
  a.*,
  ISNULL(ah.ValidFrom, a.ValidFrom) AS TimeCreated
FROM Account a
LEFT JOIN (
    SELECT ah.*,
      rn = ROW_NUMBER() OVER (PARTITION BY ah.AccountNo ORDER BY ah.ValidFrom)
    FROM Account_History ah
) ah ON ah.AccountNo = a.AccountNo AND ah.rn = 1;

You can also do this as an APPLY or scalar subquery. This may or may not be faster depending on the cardinalities involved. You should try both options.

SELECT
  a.*,
  ISNULL(ah.ValidFrom, a.ValidFrom) AS TimeCreated
FROM Account a
OUTER APPLY (
    SELECT TOP (1) ah.*
    FROM Account_History ah
    WHERE ah.AccountNo = a.AccountNo
    ORDER BY ah.ValidFrom
) ah;