clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799303 fiddles created (41680 in the last week).

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'); 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;
7 rows affected
event_date person_id event_flag rn
2019-09-10 A X 1
2019-09-11 A X 1
2019-09-12 A Y 2
2019-09-13 A X 3
2019-09-14 A X 3
2019-09-15 A X 3
2019-09-16 A Y 4
 hidden batch(es)