Create data ranges based on list of dates

Question

I have a list of dates like this (no gaps, each calendar date):

DateKey	Valid
2021-01-01	1
2021-01-02	1
2021-01-03	1
2021-01-04	0
2021-01-05	0
2021-01-06	1
2021-01-07	1

I would like to convert them using T-SQL to date ranges considering valid dates only.

So the results would be:

ValidFrom	ValidTo
2021-01-01	2021-01-03
2021-01-06	2021-01-07

Grouping simply by Valid flag rtutns wrong results:

select  min(dateKey),max(dateKey)
from #t 
group by Valid

If I knew how to assign a unique value for each continuous segment of valid dates, that would solve my problem. Is there anyone that can help me with this?

user_1570000 · Answer

Just another option using the window function sum() over()

Select ValidFrom = min(DateKey)
      ,ValidTo   = max(DateKey)
 From (
        Select *
              ,Grp = sum(case when Valid=0 then 1 else 0 end) over (order by DateKey)
        from YourTable
      ) A
 Where Valid=1
 Group By Grp

Returns

ValidFrom   ValidTo
2021-01-01  2021-01-03
2021-01-06  2021-01-07

user_4111090 · Answer

Something like the following may work for you:

DECLARE @Dates TABLE (Dt DATE, Valid BIT)
INSERT @Dates
VALUES('2021-01-01',    1),
      ('2021-01-02',    1),
      ('2021-01-03',    1),
      ('2021-01-04',    0),
      ('2021-01-05',    0),
      ('2021-01-06',    1),
      ('2021-01-07',    1)


SELECT MIN(dt.Dt) AS BeginRange,
       MAX(dt.Dt) AS EndRange
FROM (
        SELECT d.Dt,
            DATEDIFF(D, ROW_NUMBER() OVER(ORDER BY d.Dt), d.Dt) AS DtRange
        FROM @Dates d
        WHERE Valid = 1
    ) AS dt
GROUP BY dt.DtRange;

user_11378237 · Answer

I think I've just found the solution of my problem:

https://dba.stackexchange.com/questions/197972/convert-list-of-dates-in-a-date-range-in-sql-server

DECLARE @t TABLE (dt DATE);

INSERT INTO @t (dt)
VALUES ('20180202')
    ,('20180203')
    ,('20180204')
    ,('20180205')
    ,('20180209')
    ,('20180212')
    ,('20180213');

WITH c
AS (
    SELECT dt
        ,dateadd(day, - 1 * dense_rank() OVER (orderby dt), dt) AS grp
    FROM @t
    )
SELECT min(dt) AS start_range
    ,max(dt) AS end_range
FROM c
GROUP BY grp;

Create data ranges based on list of dates

3 Answers