[Solved] Grouping ranges of data

by


Since we’re sorting by StartDate and EndDate, we should be able to use the minimum of this and all future start dates and the maximum of this and all past end dates. This is just one more step beyond what Bert Wagner published.

DROP TABLE IF EXISTS #OverlappingDateRanges;
CREATE TABLE #OverlappingDateRanges (StartDate date, EndDate date);

INSERT INTO #OverlappingDateRanges
SELECT '8/24/2017', '9/23/2017'  UNION ALL
SELECT '8/24/2017', '9/20/2017'  UNION ALL 
SELECT '9/23/2017', '9/27/2017'  UNION ALL 
SELECT '9/25/2017', '10/10/2017' UNION ALL
SELECT '10/17/2017', '10/18/2017' UNION ALL 
SELECT '10/25/2017', '11/3/2017'  UNION ALL
SELECT '11/2/2017', '11/4/2017' UNION ALL
SELECT '10/27/2017', '11/15/2017' UNION ALL
SELECT '11/5/2017', '11/10/2017'


;
WITH
Ranges as (
    SELECT MIN(StartDate) OVER (ORDER BY StartDate, EndDate ROWS BETWEEN 0 FOLLOWING AND UNBOUNDED FOLLOWING) as StartDate
    , MAX(EndDate) OVER (ORDER BY StartDate, EndDate ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) as EndDate
    FROM #OverlappingDateRanges
),
Groups as (
    SELECT StartDate
    , EndDate
    , LAG(StartDate,1) OVER (ORDER BY StartDate, EndDate) AS PreviousStartDate
    , LAG(EndDate,1) OVER (ORDER BY StartDate, EndDate) AS PreviousEndDate
    FROM Ranges
),
Islands as (
    SELECT StartDate
    , EndDate
    , CASE WHEN PreviousEndDate >= StartDate THEN 0 ELSE 1 END AS IslandStartInd
    , SUM(CASE WHEN PreviousEndDate >= StartDate THEN 0 ELSE 1 END) OVER (ORDER BY StartDate, EndDate) AS IslandId
    FROM Groups
)

SELECT MIN(StartDate) AS IslandStartDate
, MAX(EndDate) AS IslandEndDate
FROM Islands
GROUP BY IslandId
ORDER BY IslandStartDate;

IslandStartDate IslandEndDate
--------------- -------------
2017-08-24      2017-10-10
2017-10-17      2017-10-18
2017-10-25      2017-11-15

I’m not finding any start/end combinations that are causing problems. I’ll let this marinate for a few days before marking it as correct in case somebody can poke some holes in it.

solved Grouping ranges of data