Eirikur Eiriksson (11/12/2014)

---

Quick solution for educational and entertainment purposes (read "spanner in the works") using slight modification of Jack Corbett's data preparation.

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.TBL_DATES') IS NOT NULL DROP TABLE dbo.TBL_DATES;

CREATE TABLE dbo.TBL_DATES

(

DATE_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,id CHAR(3) NOT NULL

,StartDate DATE NOT NULL

,EndDate DATE

);

INSERT INTO dbo.TBL_DATES

(id, StartDate, EndDate)

VALUES

('id1', '20110101', '20130930'),

('id1', '20131001', '20141231'),

('id1', '20140101', NULL),

('id2', '20110101', '20130930'),

('id2', '20131001', NULL),

('id3', '20110101', '20130930'),

('id3', '20131001', '20131231'),

('id3', '20140101', NULL),

('id4', '20110101', '20130930'),

('id4', '20131001', '20131231'),

('id5', '20131001', '20141231'),

('id5', '20140101', NULL);

;WITH BASE_DATA AS

(

SELECT

ROW_NUMBER() OVER

(

PARTITION BY D.id

ORDER BY ISNULL(D.EndDate,D.StartDate)

) AS END_RID

,ROW_NUMBER() OVER

(

PARTITION BY D.id

ORDER BY D.StartDate

) AS START_RID

,D.id

,D.StartDate

,D.EndDate

FROM dbo.TBL_DATES D

)

,GROUPED_DATA AS

(

SELECT

BD.id

,BD.START_RID + BD.END_RID AS GR_ID

,ROW_NUMBER() OVER

(

PARTITION BY BD.id

ORDER BY BD.START_RID

) AS OL_RID

,BD.StartDate

,BD.EndDate

FROM BASE_DATA BD

WHERE BD.START_RID <> BD.END_RID

)

SELECT

GD.id

,GD.GR_ID

,GD.OL_RID

,GD.StartDate

,GD.EndDate

FROM GROUPED_DATA GD;

Results

id GR_ID OL_RID StartDate EndDate

---- ------ ------- ---------- ----------

id1 5 1 2013-10-01 2014-12-31

id1 5 2 2014-01-01 NULL

id5 3 1 2013-10-01 2014-12-31

id5 3 2 2014-01-01 NULL