Merging Overlapping Date Ranges
SQL Script to Merge overlapping date ranges.
SELECT [S1].[Category]
,[S1].[StartDate]
,MIN([T1].[EndDate]) AS [EndDate]
FROM [dbo].[DateRanges] AS [S1]
INNER JOIN [dbo].[DateRanges] AS [T1]
ON (
[S1].[Category] = [T1].[Category]
AND [S1].[StartDate] <= [T1].[EndDate]
AND NOT EXISTS (
SELECT 1
FROM [dbo].[DateRanges] AS [T2]
WHERE [T1].[Category] = [T2].[Category]
AND [T1].[EndDate] >= [T2].[StartDate]
AND [T1].[EndDate] < [T2].[EndDate]
)
)
WHERE NOT EXISTS (
SELECT 1
FROM [dbo].[DateRanges] AS [S2]
WHERE [S1].[Category] = [S2].[Category]
AND [S1].[StartDate] > [S2].[StartDate]
AND [S1].[StartDate] <= [S2].[EndDate]
)
GROUP BY [S1].[Category]
,[S1].[StartDate];
Sample Data used in the demos:
category | start_date | end_date |
---|---|---|
A | 2010-01-01 | 2010-04-01 |
A | 2010-03-01 | 2010-07-01 |
A | 2010-06-01 | 2010-08-01 |
A | 2010-08-01 | 2010-10-01 |
B | 2010-06-01 | 2010-08-01 |
B | 2010-08-01 | 2010-09-01 |
B | 2010-09-01 | 2010-11-01 |
C | 2010-08-01 | 2010-09-01 |
C | 2010-09-01 | 2010-10-01 |
C | 2010-10-01 | 2010-11-01 |
C | 2010-10-01 | 2010-12-01 |
A | 2010-11-01 | 2010-12-01 |
A | 2010-11-01 | 2011-01-01 |
A | 2011-01-01 | 2011-02-01 |
A | 2011-02-01 | 2011-06-01 |
A | 2011-06-01 | 2011-08-01 |
A | 2011-08-01 | 2011-10-01 |
A | 2011-10-01 | 2011-12-01 |