How do I find the date before a value changes in T-SQL?

Question

So I have a following table:

CreatedAt                    ID    NewStatus    OldStatus
2023-03-08 13:34:57.0000000 1645    Draft        NULL
2023-03-22 19:58:51.0000000 1645    Active       Draft
2023-04-29 05:59:02.0000000 1645    Closed      Active
2023-05-08 14:50:29.0000000 1645    Awarded     Closed
2023-05-08 14:53:34.0000000 1645    ConfirmationStarted Awarded
2023-05-08 17:53:55.0000000 1645    ConfirmationDone    ConfirmationStarted

I want to fetch the date where this ID got "Closed" before "ConfirmationStarted" so in this case i would get the following result

ID    xdate
1645  2023-04-29 05:59:02.0000000

I wrote a T-SQL query:

WITH StatusChanges AS (
    SELECT 
        ID,
        newstatus,
        oldstatus,
        CreatedAt,
        LEAD(newstatus) OVER (PARTITION BY IDORDER BY CreatedAt) AS next_status

    FROM 
        tableA 
        
)
SELECT 
    ID,
   MAX(CreatedAt) AS xdate
FROM 
    StatusChanges
WHERE 
    next_status = 'Confirmation Started' and OldStatus = 'Closed'
GROUP BY 
    ID;

this query works well for most cases such as this ID

CreatedAt                      ID     NewStatus     OldStatus
2022-05-25 23:17:44.0000000 147 Active  Closed
2022-05-28 05:59:02.0000000 147 Closed  Active
2022-05-30 20:48:53.0000000 147 Active  Closed
2022-06-18 05:59:01.0000000 147 Closed  Active
2022-06-21 20:09:48.0000000 147 Active  Closed
2022-06-25 05:59:01.0000000 147 Closed  Active
2022-07-13 00:02:47.0000000 147 ConfirmationStarted Closed
2022-07-15 15:33:30.0000000 147 ConfirmationDone    ConfirmationStarted

and I get my desired date: 2022-06-25 05:59:01.0000000

how do I handle the case with 1645 ID?

Answer 1

This can be done using GROUP BY and HAVING clauses, the condition is that that the row indicating Closed status must precede the row indicating ConfirmationStarted status :

SELECT ID, MAX(CASE WHEN NewStatus = 'Closed' THEN CreatedAt END) AS xdate
FROM mytable
WHERE NewStatus in ('Closed','ConfirmationStarted')
GROUP BY ID
HAVING MAX(CASE WHEN NewStatus = 'Closed' THEN CreatedAt END)
       < MAX(CASE WHEN NewStatus = 'ConfirmationStarted' THEN CreatedAt END)

For this dataset :

CREATE TABLE mytable (
  CreatedAt    datetime,                
  ID           int,
  NewStatus    varchar(30),
  OldStatus    varchar(30)
);

INSERT INTO mytable VALUES
('2023-03-08 13:34:57', 1645,    'Draft',        NULL),
('2023-03-22 19:58:51', 1645,    'Active',      'Draft'),
('2023-04-29 05:59:02', 1645,    'Closed' ,     'Active'),
('2023-05-08 14:50:29', 1645,    'Awarded',     'Closed'),
('2023-05-08 14:53:34', 1645,    'ConfirmationStarted', 'Awarded'),
('2023-05-08 17:53:55', 1645,    'ConfirmationDone',    'ConfirmationStarted'),
('2022-05-25 23:17:44', 147, 'Active',  'Closed'),
('2022-05-28 05:59:02', 147, 'Closed',  'Active'),
('2022-05-30 20:48:53', 147, 'Active',  'Closed'),
('2022-06-18 05:59:01', 147, 'Closed',  'Active'),
('2022-06-21 20:09:48', 147, 'Active',  'Closed'),
('2022-06-25 05:59:01', 147, 'Closed',  'Active'),
('2022-07-13 00:02:47', 147, 'ConfirmationStarted', 'Closed'),
('2022-07-15 15:33:30', 147, 'ConfirmationDone',    'ConfirmationStarted');

Results :

ID      xdate
147     2022-06-25 05:59:01.000
1645    2023-04-29 05:59:02.000

Demo here