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
Defintely a better solution than mine. Thanks Erikur.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question