Merging Overlapping Date Ranges

Date posted: Post reading time: 2 minutes
Convert overlapping data from this
To this

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