Group by event using row_number

May 3, 2022

I have this table:

CREATE TABLE #Data 
(
    Event_Date date,
    Person_ID varchar(1), 
    Event_flag varchar(1)
)

INSERT INTO #Data (Event_date, Person_ID, Event_flag)
VALUES
    ('20190910', 'A', 'X'),
    ('20190911', 'A', 'X'),
    ('20190912', 'A', 'Y'),
    ('20190913', 'A', 'X'),
    ('20190914', 'A', 'X'),
    ('20190915', 'A', 'X'),
    ('20190916', 'A', 'Y')

How to get result using row_number or loop, to get same row_numbers by the same event, just on other event row_number get +1

Person_ID   Event_flag  Rn
--------------------------
A           X           1
A           X           1
A           Y           2
A           X           3
A           X           3
A           X           3
A           Y           4

>Solution :

Appreciate that the counter in the expected Rn column should increment by one each time the event flag changes value, for a given person, as ordered by the event date. We can use LAG here to construct an increment column, which when summed by event date, will generate the sequence you want.

WITH cte AS (
    SELECT *, CASE WHEN Event_flag =
              LAG(Event_flag) OVER (PARTITION BY Person_ID ORDER BY Event_date)
                   THEN 0 ELSE 1 END AS flag
    FROM Data
)

SELECT Event_date, Person_ID, Event_flag,
       SUM(flag) OVER (ORDER BY Event_date) AS Rn
FROM cte
ORDER BY Event_date;