Counter Number of Times in State

I want to add a column which tells me the iteration count of how many times we’ve been in a state, ordered by date.

We see we’re in state 1 for the first time, state 2 for the first time, then BACK to state 1 (iteration is now 2), the Iteration stays the same until we change state.

I’d like to calculate column ‘Iteration’

Thanks in advance if anyone can help!

*edit: changed column name to State from Group to avoid confusion

Date	State	Iteration
2021-01-01	1	1
2021-01-02	1	1
2021-01-03	2	1
2021-01-04	1	2
2021-01-06	1	2
2021-01-07	1	2
2021-01-08	2	2
2021-01-09	2	2
2021-01-10	3	1
2021-01-11	3	1

Answer

One method would be to use LAG to see if the value has changed, and if it has, increment the counter, then do a windowed sum per group:

WITH Lags AS(
    SELECT [Date],
           [Group], --GROUP is a reserved keyword, and should not be used for object names
           CASE [Group] WHEN LAG([Group]) OVER (ORDER BY [date]) THEN 0 ELSE 1 END AS Increment --GROUP is a reserved keyword, and should not be used for object names
    FROM (VALUES(CONVERT(date,'20210101'),1),
                (CONVERT(date,'20210102'),1),
                (CONVERT(date,'20210103'),2),
                (CONVERT(date,'20210104'),1),
                (CONVERT(date,'20210106'),1),
                (CONVERT(date,'20210107'),1),
                (CONVERT(date,'20210108'),2),
                (CONVERT(date,'20210109'),2),
                (CONVERT(date,'20210110'),3),
                (CONVERT(date,'20210111'),3))V([Date],[Group])) --GROUP is a reserved keyword, and should not be used for object names
SELECT [Date],
       [Group],--GROUP is a reserved keyword, and should not be used for object names
       SUM(Increment) OVER (PARTITION BY [Group] ORDER BY [Date] --GROUP is a reserved keyword, and should not be used for object names
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Iteration
FROM Lags
ORDER BY [Date];