Let's assume a table has many columns and a Temporal table is logging its history. There is one field I need to know when it changes.

I need to get each value, and the moment it changed. I also need to know if it's been set NULL so this query is not giving what I need.

SELECT
    Number,
    MIN(VersionStartDate) [Date]
FROM _TABLE_ 
GROUP BY
    Number
ORDER BY
    [Date] DESC

The result should be

CodePudding user response：

Quite similar to JMabee's which appeared after I started working on it, but perhaps a bit simpler:

CREATE TABLE #d (Number INT, VersionStartDate DATETIME);
INSERT INTO #d(Number, VersionStartDate) 
VALUES
(991281 ,'2021-11-12T08:27:11'),
(991281 ,'2021-11-12T08:20:11'),
(NULL   ,'2021-11-12T07:20:11'),
(NULL   ,'2021-11-12T06:20:11'),
(771281 ,'2021-11-11T08:26:11'),
(NULL   ,'2021-11-11T08:25:11'),
(661281 ,'2021-11-10T08:24:11'),
(NULL   ,'2021-11-10T08:22:11'),
(661281 ,'2021-11-10T08:21:11'),
(551281 ,'2021-11-09T08:20:11');

WITH cte AS
(
    SELECT  Number,
            VersionStartDate,
            LAG(Number, 1) OVER (ORDER BY VersionStartDate) AS PrevNumber
    FROM    #d
)
SELECT      cte.Number,
            cte.VersionStartDate
FROM        cte
WHERE       ISNULL(cte.Number, -1) <> ISNULL(cte.PrevNumber, -1)
ORDER BY    cte.VersionStartDate DESC;

CodePudding user response：

Well this is one way to do it, I am sure there is a more eloquent way to write it, but it gets you started:

CREATE TABLE #T(Number int, VersionStartDate datetime)
INSERT INTO #T vALUES
(991281,'2021-11-12 08:27:11'),
(991281,'2021-11-12 08:20:11'),
(NULL,'2021-11-12 07:20:11'),
(NULL,'2021-11-12 06:20:11'),
(771281,'2021-11-11 08:26:11'),
(NULL,'2021-11-11 08:25:11'),
(661281,'2021-11-10 08:24:11'),
(NULL,'2021-11-10 08:22:11'),
(661281,'2021-11-10 08:21:11'),
(551281,'2021-11-09 08:20:11')


SELECT Number, MIN(VersionStartDate) VersionStartDate
FROM
(
    SELECT *, SUM(CASE WHEN ISNULL(Number,-1) <> ISNULL(LG,-1) THEN 1 ELSE 0 END) OVER(ORDER BY VersionStartDate desc) GRP
    FROM
    (
        SELECT *, LAG(Number,1,-1) OVER(ORDER BY VersionStartDate desc) LG
        FROM #T
    ) X
) Y
GROUP BY GRP,Number
ORDER BY VersionStartDate desc

CodePudding user response：

Add VersionStartDate in your group by.

SELECT
    Number,
    MIN(VersionStartDate)
FROM _TABLE_ 
GROUP BY
    Number, VersionStartDate
ORDER BY
    VersionStartDate DESC