I'd like to create and populate the following No. of Entries in Curr.Status field seen below using SQL (sql server).
ID Sequence Prev.Status Curr.Status No. of Entries in Curr.Status
9-9999-9 1 Status D Status A 1
9-9999-9 2 Status A Status A 2
9-9999-9 3 Status A Status A 3
9-9999-9 4 Status A Status A 4
9-9999-9 5 Status A Status B 1
9-9999-9 6 Status B Status B 2
9-9999-9 7 Status B Status B 3
9-9999-9 8 Status B Status A 1
9-9999-9 9 Status A Status A 2
9-9999-9 10 Status A Status C 1
9-9999-9 11 Status C Status C 2
Is there an quick way using something like row_number() --this alone doesn't appear to be sufficient-- to create the field I'm looking for?
Thanks!
You can mark the rows where status changes using LAG function, and use SUM() OVER () to assign unique number to each group. Numbering within group is trivial:
DECLARE @t TABLE (ID VARCHAR(100), Sequence INT, PrevStatus VARCHAR(100), CurrStatus VARCHAR(100));
INSERT INTO @t VALUES
('9-9999-9', 1, 'Status D', 'Status A'),
('9-9999-9', 2, 'Status A', 'Status A'),
('9-9999-9', 3, 'Status A', 'Status A'),
('9-9999-9', 4, 'Status A', 'Status A'),
('9-9999-9', 5, 'Status A', 'Status B'),
('9-9999-9', 6, 'Status B', 'Status B'),
('9-9999-9', 7, 'Status B', 'Status B'),
('9-9999-9', 8, 'Status B', 'Status A'),
('9-9999-9', 9, 'Status A', 'Status A'),
('9-9999-9', 10, 'Status A', 'Status C'),
('9-9999-9', 11, 'Status C', 'Status C');
WITH cte1 AS (
SELECT *, CASE WHEN LAG(CurrStatus) OVER(ORDER BY Sequence) = CurrStatus THEN 0 ELSE 1 END AS chg
FROM @t
), cte2 AS (
SELECT *, SUM(chg) OVER(ORDER BY Sequence) AS grp
FROM cte1
), cte3 AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY Sequence) AS SeqInGroup
FROM cte2
)
SELECT *
FROM cte3
ORDER BY Sequence