Several bookings can describe one continuous period, even when their end dates arrive out of order. Merging overlapping ranges needs the latest end seen so far, rather than only the previous row.
Define the Boundary Before Merging Overlapping Ranges
Date ranges appear in leave records, service subscriptions, and equipment reservations. Reports usually need their combined coverage. Returning every source row makes a continuous period look like several separate starts and stops.
Start by defining the endpoints. In an inclusive date range, both StartDate and EndDate belong to the period. For uninterrupted daily coverage, a period starting the next day also touches the previous period.
A half-open timestamp range includes its start and excludes its end. Two such ranges touch when the next start equals the previous end. Mixing those conventions changes the answer at precisely the boundaries users check first.
I write the boundary rule before writing the window function. A beautifully ordered result still fails when the application and report disagree about the final day. The calendar is not interested in our formatting preferences.
The examples below use inclusive dates and merge consecutive days. Window functions with ordered frames require SQL Server 2012 or later. Keep timestamp scheduling separate unless its boundary rule is explicitly adapted.
Include Nested Ranges in the Sample
A useful test needs more than two neat overlaps. Include a long range containing a short one, another range overlapping the long one, consecutive coverage, a real gap, and a second entity.
CREATE TABLE #LeaveRanges
(
RangeID int NOT NULL PRIMARY KEY,
StaffID int NOT NULL,
StartDate date NOT NULL,
EndDate date NOT NULL,
CHECK (EndDate >= StartDate)
);
INSERT #LeaveRanges
VALUES (1, 1, '20260101', '20260110'),
(2, 1, '20260103', '20260104'),
(3, 1, '20260109', '20260112'),
(4, 1, '20260113', '20260114'),
(5, 1, '20260120', '20260121'),
(6, 2, '20260103', '20260105');
SELECT RangeID, StaffID, StartDate, EndDate
FROM #LeaveRanges
ORDER BY StaffID, StartDate, EndDate, RangeID;RangeID makes the window ordering unambiguous. Equal start and end dates still receive a stable position. Partitioning by StaffID keeps one person's coverage from affecting another person's group.
The check constraint rejects inverted ranges in this demonstration. Production data also needs a decision about null endpoints and open-ended periods. Do not allow those values to acquire accidental meaning through a MAX calculation.
Carry the Furthest End Already Seen
LAG(EndDate) gives the end of the previous row. That value is insufficient when a short range sits inside a longer earlier range. The previous row can end before coverage from an older row has finished.
Use a running MAX ending one row before the current row. This gives the latest end among every preceding range in the same partition. The first row has no preceding end and starts its own group.
SELECT RangeID, StaffID, StartDate, EndDate,
MAX(EndDate) OVER
(
PARTITION BY StaffID
ORDER BY StartDate, EndDate, RangeID
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS PriorCoveredThrough
FROM #LeaveRanges
ORDER BY StaffID, StartDate, EndDate, RangeID;Compare PriorCoveredThrough with EndDate from the previous row. The nested sample makes their difference visible. A solution tested only on ranges whose ends keep increasing will miss this defect.
ROWS defines a frame over preceding rows in the stated order. It avoids treating tied order values as one peer group. The final RangeID also makes the row sequence deterministic for the subsequent running sum.
I check this intermediate result before collapsing anything. Once the source ranges become one summary row, an incorrect group boundary is harder to trace back to its cause.
Turn Real Gaps Into Group Numbers
For inclusive dates, consecutive days have a day difference of one. A difference greater than one leaves uncovered dates between ranges. DATEDIFF avoids adding a day to a maximum date value.
Flag a new group only when no earlier range exists or that gap rule holds. Then accumulate the flags with SUM. Separate window stages into CTEs because one window expression cannot be nested directly inside another.
WITH Coverage AS
(
SELECT *, MAX(EndDate) OVER
(
PARTITION BY StaffID
ORDER BY StartDate, EndDate, RangeID
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS PriorCoveredThrough
FROM #LeaveRanges
), Starts AS
(
SELECT *, CASE
WHEN PriorCoveredThrough IS NULL
OR DATEDIFF(day, PriorCoveredThrough, StartDate) > 1
THEN 1 ELSE 0 END AS StartsNewPeriod
FROM Coverage
), Numbered AS
(
SELECT *, SUM(StartsNewPeriod) OVER
(
PARTITION BY StaffID
ORDER BY StartDate, EndDate, RangeID
ROWS UNBOUNDED PRECEDING
) AS PeriodNumber
FROM Starts
)
SELECT StaffID, PeriodNumber,
MIN(StartDate) AS PeriodStart,
MAX(EndDate) AS PeriodEnd
FROM Numbered
GROUP BY StaffID, PeriodNumber
ORDER BY StaffID, PeriodStart;PeriodNumber is an intermediate grouping value, not a permanent business identifier. Adding an earlier range can renumber later periods. Do not store it as an external reference that other systems expect to remain stable.
The final MIN and MAX describe continuous coverage under your selected rule. They do not retain the individual booking IDs, reasons, or approval states. Keep the source rows when those details still matter.
Change the Rule Without Changing Its Meaning
If only literal overlap should merge, start a new group whenever StartDate exceeds PriorCoveredThrough. Consecutive inclusive dates then remain separate. Choose that rule when adjacent coverage does not represent one business period.
For half-open timestamps, use the same greater-than comparison to merge overlapping or exactly touching periods. Equality represents a continuous boundary. DATEDIFF(day) is unsuitable there because crossing midnight is not the same as leaving a gap.
How should two subscriptions touching at noon be reported? Ask that before converting timestamps to dates. Removing the time can create apparent overlap where a real gap existed during the day.
For open-ended ranges, decide whether a null end represents indefinite coverage or incomplete data. Those meanings require different handling. A running MAX ignores nulls, so the default behavior is not an open-ended business rule.
Validate Merging Overlapping Ranges Against the Source
Test the grouped output against the nested cases, tied starts, consecutive dates, and separate staff partitions. Include single-day ranges and exact duplicate ranges. Duplicates should not create an extra group, although they can indicate an input problem.
For large tables, consider an index beginning with StaffID and StartDate, followed by the remaining ordering columns. Check the actual plan for sorts and memory grants. The right index depends on the other work performed on that table.
Do not total source durations to measure combined coverage. Overlaps would be counted several times. Calculate duration from the merged periods using the chosen inclusive or exclusive convention instead.
Retain a mapping to source ranges if reviewers need to explain a period. Joining source rows to the numbered intermediate result supplies that trace. A summary is useful only when you can explain why its boundaries exist.
Keep canceled or unapproved bookings outside the coverage calculation when the report excludes them. Filtering after grouping can erase the reason two ranges became connected. Apply the business filter to the source first, then run the same coverage steps. Recheck the result after changing that filter, because removing a connecting range creates a genuine gap in an otherwise continuous period. When merging overlapping ranges, the preceding maximum end is the important boundary. Review that boundary when merging overlapping intervals with nested or equal starts.
Related reading on this blog: Preventing Overlapping Date Ranges in a Table and Gaps and Islands: Finding Missing Ranges in a Sequence.
A continuous period is not the previous row's ending, it is the furthest coverage established by every earlier range.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
