quote:

---

Originally posted by sql_server_dba

111 1 1/1/2011 1/2/2011 11
111 1 1/2/2011 1/6/2011 11
111 1 1/6/2011 1/12/2011 11
111 2 1/12/2011 1/20/2011 11
111 1 1/20/2011 12/31/9999 11

then the output should be

111 1 1/1/2011 1/12/201 11
111 2 1/12/2011 1/20/2011 11
111 1 1/20/2011 12/31/9999 11

---

quote:

---

Originally posted by visakh16

then you need to use a iterative logic like below.

http://visakhm.blogspot.com/2012/03/iterative-queries-using-common-table.html

make a start using above and in case you face any difficulty, I'll help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

DECLARE @sample TABLE (
	[columnA] INT
	, [columnB] INT
	, [Start] DATE
	, [End] DATE
	, [ColumnC] INT
	)
INSERT @sample
VALUES
	 (111, 1, '20110101', '20110201', 11)
	,(111, 1, '20110201', '20110401', 11) -- This should dissapear
	,(111, 1, '20110401', '20110601', 11)
	,(111, 2, '20110601', '20110701', 22)
	,(111, 1, '20110701', '99991230', 11) -- This is an example of an outlier
	
	

SELECT * FROM @sample

; WITH

	startDateRanges AS (
		SELECT *
		FROM @sample AS s1
		WHERE NOT EXISTS (
			SELECT 1
			FROM @sample AS s2
			WHERE
				s2.[columnA] = s1.[columnA]
				AND s2.[columnB] = s1.[columnB]
				AND s2.[ColumnC] = s1.[ColumnC]
				AND s2.[End] = s1.[Start]
				)
		)
	
	, endDateRanges AS (
		SELECT *
		FROM @sample AS s1
		WHERE NOT EXISTS (
			SELECT 1
			FROM @sample AS s2
			WHERE
				s2.[columnA] = s1.[columnA]
				AND s2.[columnB] = s1.[columnB]
				AND s2.[ColumnC] = s1.[ColumnC]
				AND s2.[Start] = s1.[End]
				)
		)
		
SELECT
	sdr.[ColumnA]
	, sdr.[columnB]
	, sdr.[Start]
	, edr.[End]
	, sdr.[ColumnC]
FROM
	startDateRanges AS sdr
	CROSS APPLY ( 
		SELECT TOP 1 [End]
		FROM endDateRanges AS edr
		WHERE edr.[columnA] = sdr.[columnA]
		  AND edr.[columnB] = sdr.[columnB]
		  AND edr.[ColumnC] = sdr.[ColumnC]
		  AND edr.[Start] >= sdr.[Start]
		ORDER BY
			edr.[End] ASC
		)
		AS edr

ColumnA     columnB     Start      End        ColumnC
----------- ----------- ---------- ---------- -----------
111         1           2011-01-01 2011-06-01 11
111         2           2011-06-01 2011-07-01 22
111         1           2011-07-01 9999-12-30 11