I’m attempting to return only the modified records in a table, using T-SQL.
This is what I’ve done thus far:
BEGIN IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test CREATE TABLE #Test (SetName nvarchar(100), [Timestamp] datetime, Value smallint) INSERT INTO #Test VALUES('Alpha', GETDATE(), 1) INSERT INTO #Test VALUES('Alpha', GETDATE(), 0) INSERT INTO #Test VALUES('Alpha', GETDATE(), 1) INSERT INTO #Test VALUES('Beta', GETDATE(), 1) INSERT INTO #Test VALUES('Beta', GETDATE(), 1) INSERT INTO #Test VALUES('Beta', GETDATE(), 1) INSERT INTO #Test VALUES('Gamma', GETDATE(), 1) INSERT INTO #Test VALUES('Gamma', GETDATE(), 0) INSERT INTO #Test VALUES('Gamma', GETDATE(), 1) SELECT * FROM #Test END;
Results:
SetName Timestamp Value Alpha 2022-05-23 12:58:41.100 1 Alpha 2022-05-23 12:58:41.101 0 Alpha 2022-05-23 12:58:41.102 1 Beta 2022-05-23 12:58:41.103 1 Beta 2022-05-23 12:58:41.104 1 Beta 2022-05-23 12:58:41.105 1 Gamma 2022-05-23 12:58:41.106 1 Gamma 2022-05-23 12:58:41.107 0 Gamma 2022-05-23 12:58:41.108 1
Expected results:
Alpha 2022-05-23 12:58:41.101 0 -- Changed from 1 to 0 Alpha 2022-05-23 12:58:41.102 1 -- Changed from 0 to 1 Gamma 2022-05-23 12:58:41.107 0 -- Changed from 1 to 0 Gamma 2022-05-23 12:58:41.108 1 -- Changed from 0 to 1
The following statement returns all the 0 to 1, and 1 to 0 records and I don’t understand why:
;WITH cte AS ( SELECT SetName, [Timestamp], Value, lag(Value, 1, -1) OVER (ORDER BY [Timestamp]) AS LastValue FROM #Test ) SELECT SetName, [Timestamp], [Value] FROM cte WHERE value <> LastValue
Advertisement
Answer
To track the changes at whole dataset, you were almost there, you just need to remove the first row by using LastValue <> -1
.
WITH CTE AS ( SELECT SetName, [Timestamp], Value, lag(Value, 1, -1) OVER (ORDER BY [Timestamp]) AS LastValue FROM #Test ) SELECT SetName, [Timestamp], [Value] FROM CTE WHERE value<>Lastvalue AND Lastvalue<> -1; --Add this filter to remove the rows which doesn't have any Lag Value
To track the changes at SetName
level.
WITH CTE AS ( SELECT SetName, [Timestamp], Value, Lag(Value, 1, -1) OVER (PARTITION BY SetName ORDER BY [Timestamp]) AS LastValue FROM #Test ) SELECT SetName, [Timestamp], [Value] FROM CTE WHERE Value<>LastValue AND LastValue <> -1;
db<>fiddle: Try here
Note: From the given dataset, modified records at SetName level and at whole dataset is same.
Assumption: Each Timestamp value is different.