SQL Server - summarize dates by a flag

Question

I'm using SQL Server 2008 (though have access to SQL 2017 if needed) and have a table like this:

DECLARE @tbl TABLE (recdate DATE, myflag BIT)

That table has rows for all dates in a range, the myflag bit will change off and on, something like this:

recdate    | myflag
2017-01-01 | 1
2017-01-02 | 1
2017-01-03 | 1
...
2017-04-03 | 1
2017-04-04 | 0
2017-04-05 | 0
..
2017-05-15 | 0
2017-05-16 | 1
etc.

but what I really need to get to is something like

period_from | period_to  | myflag
2017-01-01  | 2017-04-03 | 1
2017-04-04  | 2017-05-15 | 0
2017-05-16  | 2017-05-21 | 1

so every time myflag changes, it creates a new row and the previous row has the end date set (if that makes sense)

I'm sure there is an incredibly obvious way of doing this, but I'm about ready to bash my head against the wall.. i've gone back and forth with selects and subselects and inserts and updates into temporary tables, even trying a cursor (I know! but its a one-off query)

Answer 1

This is a gaps-and-islands problem. You can use a difference of row numbers for this purpose:

select min(recdate) as period_from, max(recdate) as period_to, flag
from (select t.*,
             row_number() over (order by recdate) as seqnum,
             row_number() over (partition by flag order by recdate) as seqnum_f
      from @tbl t
     ) t
group by (seqnum - seqnum_f), flag;

Why this works is a little tricky to explain in words. I find that if you run the subquery, you'll see why the difference is constant for the groups you are looking for.

If your dates are sequential with no gaps or duplicates or time components, you can do the slightly simpler:

select min(recdate) as period_from, max(recdate) as period_to, flag
from (select t.*,
             dateadd(day, 
                     - row_number() over (partition by flag order by recdate
                     recdate
                    ) as grp
      from @tbl t
     ) t
group by grp, flag;

This is basically the same logic as the first version.