How to Efficiently Merge Overlapping Date Intervals?

Merging Overlapping Date Intervals (Improved Solution)

Merging overlapping date intervals is a common task in data processing. While the provided solution is simple, it may not be the most efficient or comprehensive. Here's an improved solution that addresses potential edge cases:

-- Retrieve the overlapping ranges
SELECT 
       s1.StartDate,
       MIN(t1.EndDate) AS EndDate
FROM @T s1 
INNER JOIN @T t1 ON s1.StartDate <= t1.EndDate
  AND NOT EXISTS(SELECT * FROM @T t2 
                 WHERE t1.EndDate >= t2.StartDate AND t1.EndDate < t2.EndDate) 
GROUP BY s1.StartDate 

-- Exclude subintervals
EXCEPT

-- Find overlapping intervals with extended end dates
SELECT 
       s1.StartDate,
       MAX(t1.EndDate) AS EndDate
FROM @T s1 
INNER JOIN @T t1 ON s1.StartDate > t1.StartDate AND s1.StartDate <= t1.EndDate
GROUP BY s1.StartDate

This query takes into account cases where the overlaps may extend beyond the original range. The EXCEPT clause filters out these extended ranges, resulting in a more accurate and complete merged interval set.

Output:

StartDate  | EndDate
2010-01-01 | 2010-06-13
2010-06-15 | 2010-08-16
2010-11-01 | 2010-12-31

The above is the detailed content of How to Efficiently Merge Overlapping Date Intervals?. For more information, please follow other related articles on the PHP Chinese website!