Count number of consecutive grouped entries in SQL

Question

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!

Answer 1

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

Demo on DB Fiddle

Answer 2

If the Sequence is identity column then you can do :

select t.*, 
       row_number() over (partition by (Sequence - seq) order by Sequence) as [No. of Entries in Curr.Status]
from (select t.*, 
             row_number() over (partition by [Curr.Status] order by Sequence) as seq
      from table t
     ) t;

else you need to generate two row_numbers :

select t.*, 
       row_number() over (partition by (seq1- seq2) order by Sequence) as [No. of Entries in Curr.Status]
from (select t.*, 
             row_number() over (partition by id order by Sequence) as seq1
             row_number() over (partition by id, [Curr.Status] order by Sequence) as seq2
      from table t
     ) t;

Answer 3

This appears to be a Groups and Islands problem. there are plenty of examples out there on how to achieve this, however:

WITH VTE AS(
        SELECT *
        FROM (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')) V(ID, Sequence, PrevStatus,CurrStatus)),
CTE AS(            
    SELECT ID,
           [Sequence],
           PrevStatus,
           CurrStatus,
           ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Sequence]) -
           ROW_NUMBER() OVER (PARTITION BY ID,CurrStatus ORDER BY [Sequence]) AS Grp
    FROM VTE V)
SELECT ID,
       [Sequence],
       PrevStatus,
       CurrStatus,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY [Sequence]) AS Entries
FROM CTE;