I would like to start from an example:
DECLARE @tmp TABLE ([id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL)
INSERT INTO @tmp
VALUES ('2013-11-01','2013-11-10'),
('2013-11-05','2013-11-15'),
('2013-11-10','2013-11-15'),
('2013-11-10','2013-11-25'),
('2013-11-26','2013-11-29')
;WITH OverlappedDates AS
(
SELECT id, StartDate, EndDate, id AS LastId, CONVERT(DATETIME, '1900-01-01') AS NewEndDate
FROM @tmp
UNION ALL
SELECT od.id, od.StartDate, od.EndDate, t.Id AS LastId, t.EndDate AS NewEndDate
FROM OverlappedDates AS od INNER JOIN (
SELECT id, StartDate, EndDate
FROM @tmp) AS t ON od.Lastid +1 = t.id
WHERE od.EndDate BETWEEN t.StartDate AND t.EndDate
)
SELECT *
FROM OverlappedDates
WHERE LastId>id
ORDER BY id
Returns:
id StartDate EndDate LastId NewEndDate
1 2013-11-01 00:00:00.000 2013-11-10 00:00:00.000 2 2013-11-15 00:00:00.000
1 2013-11-01 00:00:00.000 2013-11-10 00:00:00.000 3 2013-11-15 00:00:00.000
1 2013-11-01 00:00:00.000 2013-11-10 00:00:00.000 4 2013-11-25 00:00:00.000
2 2013-11-05 00:00:00.000 2013-11-15 00:00:00.000 3 2013-11-15 00:00:00.000
2 2013-11-05 00:00:00.000 2013-11-15 00:00:00.000 4 2013-11-25 00:00:00.000
3 2013-11-10 00:00:00.000 2013-11-15 00:00:00.000 4 2013-11-25 00:00:00.000
If you replace last
SELECT
statement with below code
SELECT id, StartDate, MAX(LastId) AS LastId, MAX(NewEndDate) AS NewEndDate
FROM OverlappedDates
WHERE (LastId>Id AND NewEndDate>'1900-01-01')
GROUP BY id, StartDate
ORDER BY id, LastId
it would give:
id StartDate LastId NewEndDate
1 2013-11-01 00:00:00.000 4 2013-11-25 00:00:00.000
2 2013-11-05 00:00:00.000 4 2013-11-25 00:00:00.000
3 2013-11-10 00:00:00.000 4 2013-11-25 00:00:00.000
Time to draw conclusion:
- As you can see, there is more than one record with overlapping dates. It means:
- id no. 1 consumes ids 2, 3 and ends at id no. 4
- id no. 2 consumes id no. 3 and ends at id no. 4
- id no. 3 end at id no. 4
- record no. 5 does not match to your needs, does not meets the criteria
There is only one thing to do to accomplish this job: filter the data to remove records with id no. 2 and 3. That belongs to you ;)