Hoping to find something.
I have data as shown below
id month flag
111 jan 1
111 feb 1
111 mar 1
111 apr 0
111 may 0
111 jun 1
222 jan 1
222 feb 1
222 mar 0
222 apr 0
222 may 0
222 jun 1
I looking for the output as below
id month flag order
111 jan 1 1
111 feb 1 2
111 mar 1 3
111 apr 0 1
111 may 0 2
111 jun 1 1
222 jan 1 1
222 feb 1 2
222 mar 0 1
222 apr 0 2
222 may 0 3
222 jun 1 1
I tried row_number()
but the problem is we cannot break the sequence and start over. At an overall level, when ever there is a change in flag variable from 0 to 1 or 1 to 0 I need to start counting from 1 for each id separately
Assuming SQL Server, here is an example: