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

Asked 24/6, 2017 at 16:1 Answered 24/5, 2021 at 19:36

Solved sql sql-server t-sql azure-sql-database temporal-tables

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

Polygraph answered 24/6, 2017 at 16:1 Comment(11)

The shiftID never seems to change, are you sure you didn't mean statusID? – Roux 24/6, 2017 at 18:49

I think you just want to use Lag() and compare values based upon your stated request of output. – Roux 24/6, 2017 at 18:51

ShiftId is the primary key for the table – Polygraph 24/6, 2017 at 19:54

Unless you want to mess around with cursors (please don't), I would recommend doing this client-side. – Elyn 28/6, 2017 at 23:34

Relational DBMS (such as SQL Server) assume stable schema. It means that you have to explicitly write column names in the queries. The only way to write a generic code that would work for any table is to build the text of the query dynamically using metadata from INFORMATION_SCHEMA system views (or non-standard SQL Server specific version of such views) and then execute it through, say, sp_executesql. – Psychotherapy 29/6, 2017 at 0:23

In your example only the StatusID and TradeDate changed but it sounds from your q that others may be subject to change - would you be able to give a list of all the columns where the values may change and for which you want to see the changes in the query results? – Trifurcate 29/6, 2017 at 15:49

@VladimirBaranov thanks for the reply, what you say makes sense, I am not opposed to hard writing this query for each table, I was just looking for a shortcut, which it appears there is not one. – Polygraph 29/6, 2017 at 16:10

@SteveChambers, sure thing, I will post an edit in the next minute – Polygraph 29/6, 2017 at 16:10

Thanks, have updated my answer accordingly. – Trifurcate 29/6, 2017 at 16:31

@VladimirBaranov not "the only way" https://mcmap.net/q/565479/-how-to-identify-changed-values-using-a-sql-server-temporal-table – Moult 2/7, 2017 at 9:43

is there a way to mimic this type of output from MySQL? https://mcmap.net/q/128241/-is-there-a-mysql-option-feature-to-track-history-of-changes-to-records – Ger 17/11, 2019 at 17:58

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;

Moult answered 1/7, 2017 at 19:31 Comment(1)

thanks Martin, this ended up meeting my needs after some testing – Polygraph 4/7, 2017 at 18:10

This will certainly not be the best performing way but meets the requirement

Is there a way to do this dynamically so it detects the columns automatically?

Demo

WITH k
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY ShiftId ORDER BY ValidFrom) AS _RN
         FROM   KrisisShifts_ShiftTrade
        /*FOR SYSTEM_TIME ALL*/
        ),
     T
     AS (SELECT k.*,
                _colname = n.n.value('local-name(.)[1]', 'sysname'),
                _colvalue = n.n.value('text()[1]', 'nvarchar(4000)')
         FROM   k
                CROSS apply (SELECT (SELECT k.*
                                     FOR xml path('row'), elements xsinil, type)) ca(x)
                CROSS APPLY x.nodes('/row/*[not(self::_RN or self::ValidFrom or self::ValidTo)]') n(n))
SELECT T.ShiftId,
       T._colname  AS [Column],
       T._colvalue AS value,
       t.ValidFrom,
       T.ValidTo
FROM   T T
       INNER JOIN T Tnext
         ON Tnext._RN = T._RN + 1
            AND T.ShiftId = Tnext.ShiftId
            AND T._colname = Tnext._colname
WHERE  EXISTS(SELECT T._colvalue
              EXCEPT
              SELECT Tnext._colvalue)
ORDER  BY ShiftId,
          [Column],
          ValidFrom;

Moult answered 1/7, 2017 at 18:51 Comment(2)

Thanks martin! You query does work, and you are right, it does not have great performance. It took a minute+ for it to run on SQL Azure where I supplied the Shift Id of a specific record with 20 "changes" or temporal records. If I was to hard code the columns, how would I do that, the performance improvement might tbe worth it to build this out for each table. – Polygraph 2/7, 2017 at 1:15

@MartinSmith, you are right. I had a glimpse of a thought about for xml, but dismissed it and wrote a generic statement about using INFORMATION_SCHEMA. Thanks for showing this method. – Psychotherapy 2/7, 2017 at 10:25

Method

Would suggest using a stored procedure that loops through the rows using a cursor and builds up the results in a temporary table. (Since there are a manageable number of columns here I'd suggest doing the comparisons of each column value manually rather than attempting to do it dynamically as the latter would be more complex.)

Demo

Rextester demo: http://rextester.com/EEELN72555

Stored procedure SQL

CREATE PROCEDURE GetChanges(@RequestedShiftID INT)
AS
BEGIN
    DECLARE @ValidFrom DATETIME, @ValidTo DATETIME, @TradeDate DATETIME; 
    DECLARE @PrevTradeDate DATETIME, @LastActionDate DATETIME;
    DECLARE @PrevLastActionDate DATETIME;
    DECLARE @ShiftId INT, @StatusID INT, @PrevStatusID INT, @OwnerUserID INT;
    DECLARE @PrevOwnerUserID INT, @WorkerUserID INT, @PrevWorkerUserID INT;
    DECLARE @Archived INT, @PrevArchived INT;
    DECLARE @WorkerEmail VARCHAR(MAX), @PrevWorkerEmail VARCHAR(MAX);

    CREATE TABLE #Results (Id INT NOT NULL IDENTITY (1,1) PRIMARY KEY, ShiftId INT,
                           [Column] VARCHAR(255), Value VARCHAR(MAX), 
                           ValidFrom DATETIME, ValidTo DATETIME);

    DECLARE cur CURSOR FOR
    SELECT 
        ValidFrom
        , ValidTo
        , ShiftId
        , TradeDate
        , StatusID
        , [LastActionDate]
        , [OwnerUserID]
        , [WorkerUserID]
        , [WorkerEmail]
        , [Archived]
    FROM [KrisisShifts_ShiftTrade]
    FOR SYSTEM_TIME ALL
    WHERE [ShiftID] = @RequestedShiftID
    ORDER BY ValidTo Desc;

    OPEN cur;
    FETCH NEXT FROM cur INTO
        @ValidFrom
        , @ValidTo
        , @ShiftId
        , @TradeDate
        , @StatusID
        , @LastActionDate
        , @OwnerUserID
        , @WorkerUserID
        , @WorkerEmail
        , @Archived;

    WHILE @@FETCH_STATUS = 0
    BEGIN
       SET @PrevTradeDate = @TradeDate;
       SET @PrevStatusID = @StatusID;
       SET @PrevLastActionDate = @LastActionDate;
       SET @PrevOwnerUserID = @OwnerUserID;
       SET @PrevWorkerUserID = @WorkerUserID;
       SET @PrevWorkerEmail = @WorkerEmail;
       SET @PrevArchived = @Archived;

       FETCH NEXT FROM cur INTO
            @ValidFrom
            , @ValidTo
            , @ShiftId
            , @TradeDate
            , @StatusID
            , @LastActionDate
            , @OwnerUserID
            , @WorkerUserID
            , @WorkerEmail
            , @Archived;

       IF @TradeDate <> @PrevTradeDate
           INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
           VALUES (@ShiftId, 'TradeDate', @TradeDate, @ValidFrom, @ValidTo);
       IF @StatusID <> @PrevStatusID
           INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
           VALUES (@ShiftId, 'StatusID', @StatusID, @ValidFrom, @ValidTo);
       IF @LastActionDate <> @PrevLastActionDate
           INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
           VALUES (@ShiftId, 'LastActionDate', @LastActionDate, @ValidFrom, @ValidTo);
       IF @OwnerUserID <> @PrevOwnerUserID
           INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
           VALUES (@ShiftId, 'OwnerUserID', @OwnerUserID, @ValidFrom, @ValidTo);
       IF @WorkerUserID <> @PrevWorkerUserID
           INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
           VALUES (@ShiftId, 'WorkerUserID', @WorkerUserID, @ValidFrom, @ValidTo);
       IF @WorkerEmail <> @PrevWorkerEmail
           INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
           VALUES (@ShiftId, 'WorkerEmail', @WorkerEmail, @ValidFrom, @ValidTo);
       IF @Archived <> @PrevArchived
           INSERT INTO #Results (ShiftId, [Column], Value, ValidFrom, ValidTo)
           VALUES (@ShiftId, 'WorkerEmail', @WorkerEmail, @ValidFrom, @ValidTo);
    END   

    CLOSE cur;
    DEALLOCATE cur;

    SELECT ShiftId, [Column], Value, ValidFrom, ValidTo
    FROM #Results
    ORDER BY Id
END;

Note: The above only includes the columns that were in the example in the question. The list of columns that could change in the recent edit was wider than this but the others could of course be added in just the same way.

Trifurcate answered 29/6, 2017 at 16:13 Comment(0)

Regarding the @Martin Smith "WITH T" solution (answered Jul 1 '17 at 19:31), there was not enough test data. We can modify the test data to have an update to OwnerUserID (from 55 to 45) at 2017-06-21 00:22:22 (in the middle of the existing range for (StatusID = 2)):

VALUES
('2017-06-21 00:26:44', '9999-12-31 23:59:59', 27, '2017-01-27', 3, '2017-01-09 16:23:39.760',45, 34, '[email protected]', 1),
('2017-06-21 00:22:22', '2017-06-21 00:26:44', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',45, 34, '[email protected]', 1),
('2017-06-21 00:19:35', '2017-06-21 00:22:22', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',55, 34, '[email protected]', 1),
('2017-06-21 00:19:16', '2017-06-21 00:19:35', 27, '2017-01-28', 3, '2017-01-09 16:23:39.760',55, 34, '[email protected]', 1)

Then the results are:

ShiftId     Column         value       ValidFrom                   ValidTo
----------- -------------- ----------- --------------------------- ---------------------------
27          OwnerUserID    55          2017-06-21 00:19:35.0000000 2017-06-21 00:22:22.0000000
27          StatusID       3           2017-06-21 00:19:16.0000000 2017-06-21 00:19:35.0000000
27          StatusID       2           2017-06-21 00:22:22.0000000 2017-06-21 00:26:44.0000000
27          TradeDate      2017-01-28  2017-06-21 00:19:16.0000000 2017-06-21 00:19:35.0000000

The results show an incorrect range for (StatusID = 2). The ValidFrom date should be 2017-06-21 00:19:35. The error comes from the query pulling ValidFrom from the same row as ValidTo.

Here is my enhancement of Martin's insightful start. It works by using only ValidFrom. It reports when each value started. We don't really need to show the ValidTo, because it is just the ValidFrom of the next row.

USE tempdb
;
DROP TABLE IF EXISTS KrisisShifts_ShiftTrade
;
CREATE TABLE KrisisShifts_ShiftTrade
  (
     [ValidFrom]      DATETIME2,
     [ValidTo]        DATETIME2,
     [ShiftId]        INT,
     [TradeDate]      DATE,
     [StatusID]       INT,
     [LastActionDate] DATETIME2,
     [OwnerUserID]    INT,
     [WorkerUserID]   INT,
     [WorkerEmail]    VARCHAR(16),
     [Archived]       INT
  ); 

INSERT INTO KrisisShifts_ShiftTrade
    ([ValidFrom], [ValidTo], [ShiftId], [TradeDate], [StatusID], [LastActionDate], [OwnerUserID],[WorkerUserID],[WorkerEmail], [Archived])
VALUES
    ('2017-06-21 00:26:44', '9999-12-31 23:59:59', 27, '2017-01-27', 3, '2017-01-09 16:23:39.760',45, 34, '[email protected]', 1),
    ('2017-06-21 00:22:22', '2017-06-21 00:26:44', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',45, 34, '[email protected]', 1),
    ('2017-06-21 00:19:35', '2017-06-21 00:22:22', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',55, 34, '[email protected]', 1),
    ('2017-06-21 00:19:16', '2017-06-21 00:19:35', 27, '2017-01-28', 3, '2017-01-09 16:23:39.760',55, 34, '[email protected]', 1)
;

WITH T
     AS (SELECT ValidFrom,
                ShiftId,
                TradeDate,
                StatusID,
                LastActionDate,
                OwnerUserID,
                WorkerUserID,
                WorkerEmail,
                Archived,
                nextTradeDate = LAG(TradeDate) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextStatusID = LAG(StatusID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextLastActionDate = LAG(LastActionDate) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextOwnerUserID = LAG(OwnerUserID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextWorkerUserID = LAG(WorkerUserID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextWorkerEmail = LAG(WorkerEmail) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextArchived = LAG(Archived) OVER (PARTITION BY ShiftId ORDER BY ValidFrom)
         FROM   KrisisShifts_ShiftTrade)
SELECT ShiftId,
       Colname AS [Column],
       value,
       ValidFrom
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)
ORDER  BY ShiftId,
          [Column],
          ValidFrom 
;

This does include initial values and current values (for better or worse). Every Column has one row showing same initial ValidFrom - 2017-06-21 00:19:16, and the last row for each column shows the current value.

ShiftId     Column         value                ValidFrom
----------- -------------- -------------------- -------------------
27          Archived       1                    2017-06-21 00:19:16
27          LastActionDate 2017-01-09 16:23:39  2017-06-21 00:19:16
27          OwnerUserID    55                   2017-06-21 00:19:16
27          OwnerUserID    45                   2017-06-21 00:22:22
27          StatusID       3                    2017-06-21 00:19:16
27          StatusID       2                    2017-06-21 00:19:35
27          StatusID       3                    2017-06-21 00:26:44
27          TradeDate      2017-01-28           2017-06-21 00:19:16
27          TradeDate      2017-01-27           2017-06-21 00:19:35
27          WorkerEmail    [email protected]     2017-06-21 00:19:16
27          WorkerUserID   34                   2017-06-21 00:19:16

Importantly, though, it does correctly show that (StatusID = 2) started at 2017-06-21 00:19:35 and was replaced by (StatusID = 3) at 2017-06-21 00:26:44. If you really need to see both ValidFrom and ValidTo columns, you could wrap the final query above in a CTE and query that using the LEAD function with '9999-12-31 23:59:59.99' for the "default" parameter.

Edit: I just realized that my solution, and Martin's, do not properly handle the case where the main table row is deleted and then reinserted later. The test data below represents a case where (ShiftId = 27) was deleted at 2017-07-22 00:26:55 and reinserted later at 2017-08-23 00:26:59. Thus, (StatusID = 3) did not exist between 2017-07-22 00:26:55 and 2017-08-23 00:26:59. A proper solution for this would require a ValidFrom and a ValidTo column so we could have a row for every column that has ValidTo = 2017-07-22 00:26:55 matched by another row for the same column that has ValidFrom = 2017-08-23 00:26:59 so that we can see the range where data did not exist.

VALUES
    ('2017-08-23 00:26:59', '9999-12-31 23:59:59', 27, '2017-01-27', 3, '2017-01-09 16:23:39.760',45, 34, '[email protected]', 1),
    ('2017-06-21 00:26:44', '2017-07-22 00:26:55', 27, '2017-01-27', 3, '2017-01-09 16:23:39.760',45, 34, '[email protected]', 1),
    ('2017-06-21 00:22:22', '2017-06-21 00:26:44', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',45, 34, '[email protected]', 1),
    ('2017-06-21 00:19:35', '2017-06-21 00:22:22', 27, '2017-01-27', 2, '2017-01-09 16:23:39.760',55, 34, '[email protected]', 1),
    ('2017-06-21 00:19:16', '2017-06-21 00:19:35', 27, '2017-01-28', 3, '2017-01-09 16:23:39.760',55, 34, '[email protected]', 1)

Housum answered 24/5, 2021 at 19:36 Comment(1)

kudos for adding value to a question that is already answered! I will have to try this out – Polygraph 24/5, 2021 at 22:39

-- Very interesting question.

-- Think about your desired result - column "Value" should contain values of different types (int, decimal, date, binary, varchar, ...). So you need convert values to varchar, or use sqlvariant, or binary. Then on some point you will need to recognize type of value and process it differently for the different rows

-- To get values you may try to use UNPIVOT:

SELECT someRowID, ValidTo, ValidFrom, col, val
FROM 
    (SELECT someRowID, ValidTo, ValidFrom /*, ... */,
            [TradeDate], [StatusID], [LastActionDate], [AllowedRankID], [OwnerUserID], [OwnerEmail], [OwnerLocationID], [OwnerRankID], [OwnerEmployeeID], [WorkerUserID], [WorkerEmail], [WorkerLocationID], [WorkerRankID], [WorkerPlatoonID], [WorkerEmployeeID], [IsPartialShift], [Detail], [LastModifiedByUserID], [Archived], [UpdatedDate]
     FROM ... ) AS p
UNPIVOT    
    (val FOR col IN ([TradeDate], [StatusID], [LastActionDate], [AllowedRankID], [OwnerUserID], [OwnerEmail], [OwnerLocationID], [OwnerRankID], [OwnerEmployeeID], [WorkerUserID], [WorkerEmail], [WorkerLocationID], [WorkerRankID], [WorkerPlatoonID], [WorkerEmployeeID], [IsPartialShift], [Detail], [LastModifiedByUserID], [Archived], [UpdatedDate])
) AS unpvt

Then similary UNPIVOT previous values

... and join results as

SELECT ...
FROM prevVals
INNER JOIN vals 
        ON vals.someRowID = prevVals.someRowID 
       AND vals.col = prevVals.col
WHERE vals.val <> prevVals.val        -- yes, I know here can be a problem (NULLs, types)

This is just an idea and I hope it will help

Kawasaki answered 30/6, 2017 at 15:6 Comment(0)

Try not to use temporal Table feature:). Try trigger to check changes - it's much more easier and much shorter.

Create image of your table with timestamp and dml type column (row_id, s__dml_dt, s__dml_type + all your columns from source table) for all dml types (i,u,d).

create trigger dbo.KrisisShifts_ShiftTrade on dbo.KrisisShifts_ShiftTrade
after insert as
begin
     insert into dbo.KrisisShifts_ShiftTrade_logtable
     select getdate() s__dml_dt, 'i' s__dml_type, * from inserted
     -- for udpate select getdate() s__dml_dt, 'i' s__dml_type, * from inserted
     -- for delete select getdate() s__dml_dt, 'd' s__dml_type, * from deleted
end

Now after insert/delete/update you can check all your historical values. If you would like pivoted result you can easily create view with pivot for dbo.KrisisShifts_ShiftTrade_logtable.

Script to log all tables in database (it'll create tables with prefix r_).

declare @table sysname
declare @nl varchar(2)
declare @create_table int
declare @cmd varchar(max)
declare @trgname sysname
declare c_tables cursor for
    select table_name,
             case
                when exists (
                  select 2
                     from information_schema.tables
                    where table_name = 'r_'+ot.table_name
                  ) then 0
                else 1
             end create_table
      from information_schema.tables ot 
     where table_type = 'BASE TABLE'
        and table_name not like 'r[_]%'
        --and table_name like @tblfilter

open c_tables
fetch next from c_tables into @table,@create_table
while @@fetch_status=0
begin
   -- logovaci tabulka
    if @create_table=1
    begin
        set @cmd = 'create table r_'+@table+'(s__row_id int not null identity(1,1),s__dml_dt datetime not null,s__dml_type char(1) not null'
        select @cmd = @cmd + char(13)+char(10)+','+column_name+' '+data_type+isnull('('+case when character_maximum_length<0 then 'max' else cast(character_maximum_length as varchar) end+')','')+' null' from information_schema.columns where table_name=@table order by ordinal_position
        set @cmd = @cmd + ')'
        exec(@cmd)

        exec('create index i_s__dml_dt on r_'+@table+' (s__dml_dt)')
    end

    -- delete trigger
    set @trgname = 'trg_'+@table+'_dl_del'
    if object_id(@trgname) is not null exec('drop trigger '+@trgname)
    exec('
        create trigger '+@trgname+' on '+@table+' after delete as
        begin
          insert into r_'+@table+' select getdate(),''d'',t.* from deleted t
        end
    ')
    -- insert trigger
    set @trgname = 'trg_'+@table+'_dl_ins'
    if object_id(@trgname) is not null exec('drop trigger '+@trgname)
    exec('
        create trigger '+@trgname+' on '+@table+' after insert as
        begin
          insert into r_'+@table+' select getdate(),''i'',t.* from inserted t
        end
    ')
    -- update trigger
    set @trgname = 'trg_'+@table+'_dl_upd'
    if object_id(@trgname) is not null exec('drop trigger '+@trgname)
    exec('
        create trigger '+@trgname+' on '+@table+' after update as
        begin
          insert into r_'+@table+' select getdate(),''u'',t.* from deleted t
        end
    ')


    fetch next from c_tables into @table,@create_table
end
close c_tables
deallocate c_tables

Almanac answered 1/7, 2017 at 19:26 Comment(3)

thanks for the feedback, however, the question was how to query the temporal tables, not whether to use them or not. That would be another discussion, but seeing as they are built right into SQL server they are as fast as any trigger, capture the record state at any given time, and provide and easy way for a point in time restore of record. Also, "easier and shorter" is relative. I set up temporal tables on my DB in less than a couple minutes, for the first time. I just want a different way to query the results of the tables. Do you know of any other reason not to use temporal tables? – Polygraph 2/7, 2017 at 1:5

Of course - triggers are more user friendly, can be temporary disabled, you can make your own schema, are friendly to previous versions of sql server, you can filter and log only selected things. With triggers you can easily log thousands of tables in couple of minutes with no overhead (CDC adds at least 34 bytes per row), you can check referencial integrity after changes, add bussiness logic etc. But for one table, it doesn't matter.. – Almanac 3/7, 2017 at 19:57

great, things for me to consider, however, this project is currently committed to temporal tables and has no need to consider previous versions of SQL. Thanks for your feedback – Polygraph 3/7, 2017 at 21:51

-1

How much storage space do you have?

The last time I did something like this we inserted new rows for each changed column in a separate change log table. We did it using client-side logic, but you could get the same effect with a trigger.

This takes up a lot of room and slows down your writes, but it does give you fast read access to the change log.

P.S. We didn't have a general solution, so we only did it for the one table that needed UI support. Everything else used pseudo-temporal tables. (Old version of SQL Server.)

Elyn answered 28/6, 2017 at 23:37 Comment(2)

hello Jonathan, thanks for the response, I was hoping to avoid a trigger here because the new Temporal tables already handle tracking changes for each version of a record (and are quite fast). What I want here is a way to query the historical versions (by the supplied primary key) and only returned the values that changed in each historical record. – Polygraph 29/6, 2017 at 16:8

I want that too, but I don't see how to do it without messing around with cursors. – Elyn 29/6, 2017 at 22:22

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

WHAT I WANT:

QUESTION:

NOTE ON NEW TAG:

Recommended topics

Hot tags