How to identify changed values using a SQL Server temporal table?

Question

I have a SQL Azure table and I have turned on the new Temporal Table feature (New to SQL Server 2016 and SQL Azure v12). This feature creates another table to track all changes to the primary table (I included a link to the docs about temporal tables at the bottom of my question). You can use the special query language to get this history. Note the FOR SYSTEM_TIME ALL in the following query:

SELECT 
    ValidFrom
    , ValidTo
    , ShiftId
    , TradeDate
    , StatusID
    , [LastActionDate]
    , [OwnerUserID]
    , [WorkerUserID]
    , [WorkerEmail]
    , [Archived]
FROM [KrisisShifts_ShiftTrade] 
FOR SYSTEM_TIME ALL
WHERE [ShiftID] = 27
ORDER BY ValidTo Desc

The result set looks like this:

ValidFrom                   ValidTo                     ShiftId     TradeDate  StatusID    LastActionDate          OwnerUserID WorkerUserID WorkerEmail                                        Archived
--------------------------- --------------------------- ----------- ---------- ----------- ----------------------- ----------- ------------ -------------------------------------------------- --------
2017-06-21 00:26:44.51      9999-12-31 23:59:59.99      27          2017-01-27 3           2017-01-09 16:23:39.760 45          34           [email protected]                                   1
2017-06-21 00:19:35.57      2017-06-21 00:26:44.51      27          2017-01-27 2           2017-01-09 16:23:39.760 45          34           [email protected]                                   1
2017-06-21 00:19:16.25      2017-06-21 00:19:35.57      27          2017-01-28 3           2017-01-09 16:23:39.760 45          34           [email protected]                                   1

Using the SYSTEM_TIME FOR ALL The temporal Table returns the current record from the primary table, which is the first one, and the remaining records are previous versions of that record stored in the tracking table. (you can see the validFrom and ValidTo columns, obviously the time the record was the current record) In this case, the tracking table that keeps the historical records is called KrisisShifts_ShiftTrade_History

WHAT I WANT:

I want to build a query that just highlights the changes made at each historical point. Notice that the second record has a different StatusID and that the Third record has a different TradeDate

I want to product a result set like below (I imagne I will ignore the first or current record because it obviously is not chnaged):

DESIRED RESULT:

ShiftId      Column          Value             ValidFrom                   ValidTo
----------  -------------  ------------------- --------------------------- --------------------------
27          StatusId       2                   2017-06-21 00:19:35.57      2017-06-21 00:26:44.51
27          TradeDate      2017-01-28          2017-06-21 00:19:35.57      2017-06-21 00:26:44.51   

I am not sure how to accomplish this. Or I am open to another solution. I want to be able to quickly view the changes for each record compared to the original record.

I tried to unpivot the results to compare them, but I was unable to get that to work because the shift ID is the same for every row. I would love to show more work here, but I am really stuck.

EDIT 1:

I have been able to isolate the changes for just one column in the following query using lag(). I could union this query with a similar one for each column I want to track, however, this is a lot of work and has to be built for each table. Is there a way to do this dynamically so it detects the columns automatically?

StatusID change history query:(I isolate the records to a shiftId of 27 just for testing)

SELECT 'SHIFT STATUS'  as ColumnName, t1.RecVersion, t1.ShiftID, t1.ValidFrom, t1.ValidTo, t1.StatusId
, (SELECT [Title] FROM [dbo].[KrisisShifts_Status] WHERE [dbo].[KrisisShifts_Status].[StatusID] = t1.StatusId) AS RecStatus
FROM
    (SELECT TOP 100 PERCENT 
        ROW_NUMBER() OVER(PARTITION BY ShiftId ORDER BY ValidTo ASC) AS RecVersion -- reverse sorting the ValidTo date gives "version count" to column changes
        , t2.ValidTo
        , t2.ValidFrom
        , t2.ShiftID
        , t2.StatusId
        , LAG(StatusId,1,0) OVER (ORDER BY ValidTo DESC) AS PrevStatusId
    FROM [KrisisShifts_ShiftTrade] 
    FOR SYSTEM_TIME ALL AS t2

    ORDER BY t2.ValidTo Desc
    ) AS t1
WHERE
    (t1.StatusId <> t1.PrevStatusId)
    AND
    SHIFTID = 27
ORDER BY t1.ValidTo DESC

RESULTS of query:

ColumnName   RecVersion           ShiftID     ValidFrom                   ValidTo                     StatusId    RecStatus
------------ -------------------- ----------- --------------------------- --------------------------- ----------- --------------------------------------------------
SHIFT STATUS 3                    27          2017-06-21 00:26:44.51      2017-06-25 14:09:32.37      3           Confirmed
SHIFT STATUS 2                    27          2017-06-21 00:19:35.57      2017-06-21 00:26:44.51      2           Reserved
SHIFT STATUS 1                    27          2017-06-21 00:19:16.25      2017-06-21 00:19:35.57      3           Confirmed

END EDIT 1:

QUESTION:

Can someone help me isolate just the changed data in columns from the previous record for each shiftId in the temporal table result set?

Thanks in advance

EDIT # 2:

The following is a list of all the columns I want to "watch for changes" from this table:

[TradeDate] [StatusID] [LastActionDate] [AllowedRankID] [OwnerUserID] [OwnerEmail] [OwnerLocationID] [OwnerRankID] [OwnerEmployeeID] [WorkerUserID] [WorkerEmail] [WorkerLocationID] [WorkerRankID] [WorkerPlatoonID] [WorkerEmployeeID] [IsPartialShift] [Detail] [LastModifiedByUserID] [Archived] [UpdatedDate]

END EDIT 2:

NOTE ON NEW TAG:

I created a new tag for temporal tables as there is not one. The following has the description of them if someone with more reputation wants to add it tot he tag's details.

MS Docs on Temporal Tables

Answer 1

You can also use CROSS APPLY to UNPIVOT.

It should be noted that the ValidFrom and ValidTo refer to the validity of the row version itself not neccessarily the column value. I believe this is what you are requesting but this may be confusing.

Demo

WITH T
     AS (SELECT ValidFrom,
                ValidTo,
                ShiftId,
                TradeDate,
                StatusID,
                LastActionDate,
                OwnerUserID,
                WorkerUserID,
                WorkerEmail,
                Archived,
                nextTradeDate = LEAD(TradeDate) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextStatusID = LEAD(StatusID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextLastActionDate = LEAD(LastActionDate) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextOwnerUserID = LEAD(OwnerUserID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextWorkerUserID = LEAD(WorkerUserID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextWorkerEmail = LEAD(WorkerEmail) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextArchived = LEAD(Archived) OVER (PARTITION BY ShiftId ORDER BY ValidFrom)
         FROM   KrisisShifts_ShiftTrade)
SELECT ShiftId,
       Colname AS [Column],
       value,
       ValidFrom,
       ValidTo
FROM   T
       CROSS APPLY ( VALUES 
                    ('TradeDate', CAST(TradeDate AS NVARCHAR(4000)), CAST(nextTradeDate AS NVARCHAR(4000))),
                    ('StatusID', CAST(StatusID AS NVARCHAR(4000)), CAST(nextStatusID AS NVARCHAR(4000))),
                    ('LastActionDate', CAST(LastActionDate AS NVARCHAR(4000)), CAST(nextLastActionDate AS NVARCHAR(4000))),
                    ('OwnerUserID', CAST(OwnerUserID AS NVARCHAR(4000)), CAST(nextOwnerUserID AS NVARCHAR(4000))),
                    ('WorkerUserID', CAST(WorkerUserID AS NVARCHAR(4000)), CAST(nextWorkerUserID AS NVARCHAR(4000))),
                    ('WorkerEmail', CAST(WorkerEmail AS NVARCHAR(4000)), CAST(nextWorkerEmail AS NVARCHAR(4000))),
                    ('Archived', CAST(Archived AS NVARCHAR(4000)), CAST(nextArchived AS NVARCHAR(4000)))
                   ) CA(Colname, value, nextvalue)
WHERE  EXISTS(SELECT value
              EXCEPT
              SELECT nextvalue)
       AND ValidTo <> '9999-12-31 23:59:59'
ORDER  BY ShiftId,
          [Column],
          ValidFrom;

If you did want the validity at column level you could use (Demo)

WITH T1 AS
(
SELECT *, 
       ROW_NUMBER() OVER (PARTITION BY ShiftId, colname ORDER BY ValidFrom)  
       - ROW_NUMBER() OVER (PARTITION BY ShiftId, colname, Colvalue ORDER BY ValidFrom)  AS Grp,
       IIF(DENSE_RANK() OVER (PARTITION BY ShiftId, colname ORDER BY Colvalue) + 
        DENSE_RANK() OVER (PARTITION BY ShiftId, colname ORDER BY Colvalue DESC) = 2, 0,1) AS HasChanges
FROM KrisisShifts_ShiftTrade
       CROSS APPLY ( VALUES 
                    ('TradeDate', CAST(TradeDate AS NVARCHAR(4000))),
                    ('StatusID', CAST(StatusID AS NVARCHAR(4000))),
                    ('LastActionDate', CAST(LastActionDate AS NVARCHAR(4000))),
                    ('OwnerUserID', CAST(OwnerUserID AS NVARCHAR(4000))),
                    ('WorkerUserID', CAST(WorkerUserID AS NVARCHAR(4000))),
                    ('WorkerEmail', CAST(WorkerEmail AS NVARCHAR(4000))),
                    ('Archived', CAST(Archived AS NVARCHAR(4000)))
                   ) CA(Colname, Colvalue)
)
SELECT  ShiftId, colname, Colvalue, MIN(ValidFrom) AS ValidFrom, MAX(ValidTo) AS ValidTo
FROM T1
WHERE HasChanges = 1
GROUP BY ShiftId, colname, Colvalue, Grp
ORDER  BY ShiftId,
          colname,
          ValidFrom;