Find maximum overlapping period from date ranges

This fits your sample data - don't know if it will work in real time. And it could certainly use some refactoring, but I didn't have time for that.

Good Luck

select  ID2, MIN(LowStart) as LowStart, MAX(HighEnd) as HighEnd
from
(
	select  coalesce(b.id, a.id) ID2, 
		LowStart = case when a.StartDate < b.StartDate then 
                         a.StartDate else coalesce(b.StartDate, a.StartDate) end, 
		HighEnd = case when a.EndDate > b.EndDate then 
                         a.EndDate else coalesce(b.EndDate, a.EndDate) end
	from table1 a left outer join table1 b
		on a.id > b.id and
		((b.StartDate BETWEEN a.StartDate AND a.EndDate) or
		(b.EndDate BETWEEN a.StartDate and a.EndDate))
	where coalesce(b.id, a.id) in
	(
		select ID1
		from
		(
		     select  a.id as ID1, coalesce(b.id, a.id) ID2, 
			    LowStart = case when a.StartDate < b.StartDate then
                                       a.StartDate else 
                                       coalesce(b.StartDate, a.StartDate) end, 
			    HighEnd = case when a.EndDate > b.EndDate then 
                                       a.EndDate else 
                                       coalesce(b.EndDate, a.EndDate) end
		     from table1 a left outer join table1 b
		           on a.id > b.id and
			  ((b.StartDate BETWEEN a.StartDate AND a.EndDate) or
			  (b.EndDate BETWEEN a.StartDate and a.EndDate))
		) t
		where Id1 = id2
	)
) t2
group by ID2

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
(	
	--initial values
	SELECT id, StartDate, EndDate, id AS LastId, CONVERT(DATETIME, '1900-01-01') AS NewEndDate
	FROM @tmp
	----recurrent part
	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') --(LastId=Id AND NewEndDate='1900-01-01') OR 
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:

1. 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


2. 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 ;)

please try with below code


CREATE table #OrderData
(
RowIndex int,
StartDate datetime ,
Enddate datetime
)

INSERT INTO #OrderData(RowIndex, StartDate, Enddate)
SELECT ROW_NUMBER() OVER(ORDER BY StartDate, Enddate) AS RowIndex, StartDate, Enddate
from sampletable

CREATE table #RangeData
(
RowIndex int,
StartDate datetime ,
Enddate datetime
)

Declare @TotalRecord as int
Declare @RecordNumber as int
select @TotalRecord = count(RowIndex) from #OrderData


INSERT INTO #RangeData(RowIndex, StartDate, EndDate)
SELECT TOP 1 RowIndex, StartDate, Enddate FROM #OrderData ORDER BY RowIndex

SET @RecordNumber = 2
IF @TotalRecord > 1
BEGIN
WHILE(@TotalRecord -1 > 0)
BEGIN
DECLARE @StartDate As datetime
DECLARE @EndDate As datetime
DECLARE @LastID As datetime
SELECT TOP 1 @StartDate = StartDate, @EndDate = EndDate FROM #RangeData ORDER BY RowIndex DESC

IF EXISTS(SELECT * FROM #OrderData WHERE RowIndex = @RecordNumber AND CONVERT(varchar(10), StartDate, 102) BETWEEN CONVERT(varchar(10), @StartDate,102) AND CONVERT(varchar(10), @EndDate,102))
BEGIN
SELECT @EndDate = EndDate FROM #OrderData WHERE RowIndex = @RecordNumber
UPDATE #RangeData SET EndDate = @EndDate where RowIndex = @LastID
END
ELSE
BEGIN
INSERT INTO #RangeData(RowIndex, StartDate, EndDate)
SELECT RowIndex, StartDate, Enddate FROM #OrderData WHERE RowIndex = @RecordNumber
END

SET @RecordNumber = @RecordNumber + 1
SET @TotalRecord = @TotalRecord - 1
END
END
select * from #RangeData

DROP TABLE #OrderData
DROP TABLE #RangeData