IntervalMerge.sql

CREATE TABLE #BatchModeHelper (i integer NULL, INDEX CC CLUSTERED COLUMNSTORE);

SELECT 
    IntervalStart =
        ISNULL
        (
            LAG(Q1.NextStart) OVER (ORDER BY Q1.ThisFrom),
            Q1.FirstFrom
        ),
    IntervalEnd = 
        IIF 
        (
            Q1.NextStart IS NOT NULL, 
            Q1.ThisEnd,
            Q1.LastEnd
        )
FROM 
(
    SELECT
        ThisFrom = D.dfrom, 
        ThisEnd = D.dto,
        -- Remember the start of the next row because that row
        -- may get filtered out by the outer WHERE clase if it
        -- is not also the end of an interval.
        NextStart = LEAD(D.dfrom) OVER (
            ORDER BY D.dfrom, D.dto),
        -- Start point of the first interval
        FirstFrom = MIN(D.dfrom) OVER (
            ORDER BY D.dfrom, D.dto 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
        -- End point of the last interval
        LastEnd = MAX(D.dto) OVER (
            ORDER BY D.dfrom, D.dto 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM #d AS D 
    LEFT JOIN #BatchModeHelper AS B ON 0 = 1
    WHERE 
        -- Valid intervals only
        D.dto >= D.dfrom
) AS Q1
WHERE
    -- Interval ends only
    Q1.NextStart > Q1.ThisEnd
    OR Q1.NextStart IS NULL;