SQL Server table transformation-CodePudding

I have a table something like this:

due_date	flag
2019-02-01	FALSE
2019-01-01	FALSE
2018-12-01	FALSE
2018-11-01	TRUE
2018-10-01	FALSE
2018-09-01	FALSE
2018-08-01	TRUE

And the output table is:

due_date	flag	group
2019-02-01	FALSE	1
2019-01-01	FALSE	1
2018-12-01	FALSE	1
2018-11-01	TRUE	2
2018-10-01	FALSE	3
2018-09-01	FALSE	3
2018-08-01	TRUE	4

I have tried using the lag function, using this query

SELECT 
    *, 
    LAG(flag, 1, 'FALSE') OVER (ORDER BY due_date DESC) AS lag_flag
FROM 
    tableName

The result will become like this:

due_date	flag	lag_flag
2019-02-01	FALSE	FALSE
2019-01-01	FALSE	FALSE
2018-12-01	FALSE	FALSE
2018-11-01	TRUE	FALSE
2018-10-01	FALSE	TRUE
2018-09-01	FALSE	FALSE
2018-08-01	TRUE	FALSE

And right now I'm stuck at this step. Anyone can suggest to me the next steps? (please also tell me the query)

Note: I am using SQL Server

CodePudding user response：

Flag start of a group with 1 and compute the rolling total of the starting flag.

select due_date, flag, sum(f) OVER (ORDER BY due_date desc) x
from(
  select due_date, flag, 
     case when LAG(flag, 1, 'unknown') OVER (ORDER BY due_date desc) = flag then 0 else 1 end f
  from tbl
) t
ORDER BY due_date desc