By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #Test
(SetName nvarchar(100),
[Timestamp] datetime,
Value smallint)
INSERT INTO #Test VALUES('Alpha', GETDATE(), 1)
WAITFOR DELAY '00:00:00.01';
INSERT INTO #Test VALUES('Alpha', GETDATE(), 0)
WAITFOR DELAY '00:00:00.01';
INSERT INTO #Test VALUES('Alpha', GETDATE(), 1)
WAITFOR DELAY '00:00:00.01';
INSERT INTO #Test VALUES('Beta', GETDATE(), 1)
WAITFOR DELAY '00:00:00.01';
INSERT INTO #Test VALUES('Beta', GETDATE(), 1)
WAITFOR DELAY '00:00:00.01';
INSERT INTO #Test VALUES('Beta', GETDATE(), 1)
WAITFOR DELAY '00:00:00.01';
INSERT INTO #Test VALUES('Gamma', GETDATE(), 1)
WAITFOR DELAY '00:00:00.01';
INSERT INTO #Test VALUES('Gamma', GETDATE(), 0)
WAITFOR DELAY '00:00:00.01';
INSERT INTO #Test VALUES('Gamma', GETDATE(), 1);
SELECT * FROM #Test
| SetName | Timestamp | Value |
|---|---|---|
| Alpha | 2022-05-24 04:09:36.110 | 1 |
| Alpha | 2022-05-24 04:09:36.127 | 0 |
| Alpha | 2022-05-24 04:09:36.143 | 1 |
| Beta | 2022-05-24 04:09:36.157 | 1 |
| Beta | 2022-05-24 04:09:36.173 | 1 |
| Beta | 2022-05-24 04:09:36.190 | 1 |
| Gamma | 2022-05-24 04:09:36.203 | 1 |
| Gamma | 2022-05-24 04:09:36.220 | 0 |
| Gamma | 2022-05-24 04:09:36.237 | 1 |
--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
| SetName | Timestamp | Value |
|---|---|---|
| Alpha | 2022-05-24 04:09:36.127 | 0 |
| Alpha | 2022-05-24 04:09:36.143 | 1 |
| Gamma | 2022-05-24 04:09:36.220 | 0 |
| Gamma | 2022-05-24 04:09:36.237 | 1 |
--To track the changes at whole dataset
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
| SetName | Timestamp | Value |
|---|---|---|
| Alpha | 2022-05-24 04:09:36.127 | 0 |
| Alpha | 2022-05-24 04:09:36.143 | 1 |
| Gamma | 2022-05-24 04:09:36.220 | 0 |
| Gamma | 2022-05-24 04:09:36.237 | 1 |