Click here to Skip to main content
15,851,090 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want maximum period of date range that is overlapping each other and if the period is not clashing other date ranges than i want it as it is.
I have this table :

SQL
CREATE TABLE [dbo].[table1](
    [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL
)

And their respective values:
SQL
INSERT [dbo].[table1]  VALUES ( CAST('2013-11-01 00:00:00.000' AS DateTime), CAST('2013-11-10 00:00:00.000' AS DateTime))
INSERT [dbo].[table1]  VALUES ( CAST('2013-11-05 00:00:00.000' AS DateTime), CAST('2013-11-15 00:00:00.000' AS DateTime))
INSERT [dbo].[table1]  VALUES ( CAST('2013-11-10 00:00:00.000' AS DateTime), CAST('2013-11-15 00:00:00.000' AS DateTime))
INSERT [dbo].[table1]  VALUES ( CAST('2013-11-10 00:00:00.000' AS DateTime), CAST('2013-11-25 00:00:00.000' AS DateTime))
INSERT [dbo].[table1]  VALUES ( CAST('2013-11-26 00:00:00.000' AS DateTime), CAST('2013-11-29 00:00:00.000' AS DateTime))

And expected result is :
ID        StartDate                EndDate
1        1-Nov-2013          25-Nov-2013
2        26-Nov-2013        29-Nov-2013

Thanks in advance.
Posted
Comments
Maciej Los 5-Dec-13 3:13am    
If i understand you well, id no. 1 in desired result consumes ids 1 to 4 in a source table. Am i right?
Manish.Insan 5-Dec-13 6:13am    
No, it could be any record which is overlapping other. I want those maximum period of date range.

 
Share this answer
 
Comments
Maciej Los 5-Dec-13 5:02am    
Which part specifically?
Pablo Aliskevicius 5-Dec-13 5:57am    
The one about the islands. It's not exactly the same thing you've got (one date per record instead of two), but it's pretty similar.
Best wishes,
Pablo.
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
 
Share this answer
 
Comments
Manish.Insan 6-Dec-13 3:04am    
Not working in all cases.
I would like to start from an example:
SQL
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
SQL
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 ;)
 
Share this answer
 
v2
Comments
Manish.Insan 6-Dec-13 3:03am    
It's so time consuming from the performance perspective. I did it using join and loop but i am trying to enhance the performance after removing the loop.
Maciej Los 6-Dec-13 3:55am    
I don't see your code and i can't say anything about query performance. I can't imagine a solution without using loop ;( I dare to tell, it's impossible!
If my answer was helpful, please mark it as a solution (green button).
Manish.Insan 6-Dec-13 5:24am    
For these values result would be:

ID StartDate EndDate
1 1-Nov-2013 25-Nov-2013
2 26-Nov-2013 29-Nov-2013
Maciej Los 6-Dec-13 8:18am    
No, it wouldn't. Please, read my answer. Second record does not meet your criteria (does not contain overlapping dates).
Manish.Insan 9-Dec-13 2:53am    
In my question, i have also shown the expected result. if it is nor overlapping then it would be as it is.
By the way, thanks for reply.
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900