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?
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