Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 911
Home > Forums > Sql Server >

find maximum overlapping period from date ranges

Posted by Manish427 under Sql Server on 12/5/2013 | Points: 10 | Views : 3241 | Status : [Member] | Replies : 4
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
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 :

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:


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.

Manish Insan
Software Developer

Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Bandi on: 12/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down 

SELECT MinDate, MAX(EndDate)
FROM table1 t1
CROSS APPLY (SELECT MIN(StartDate) MinDate
			FROM table1 t2
			WHERE t1.StartDate BETWEEN t2.StartDate AND t2.EndDate 
			) t
GROUP BY MinDate


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Manish427, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Manish427 on: 12/5/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Chandu,
Thanx, It helped me to get the result at some extant but some where it fail when i tried for this input:
ID StartDate EndDate
1 24-Sep-2013 27-Sep-2013
2 26-Sep-2013 5-Oct-2013
3 4-Oct-2013 10-Oct-2013
4 9-Oct-2013 25-Oct-2013
5 26-Nov-2013 29-Nov-2013

Expected output is:
ID StartsDate EndDate
1 24-Sep-2013 25-Oct-2013
2 26-Nov-2013 29-Nov-2013

But it returned:
ID StartDate EndDate
1 24-Sep-2013 5-Oct-2013
2 26-Sep-2013 10-Oct-2013
3 4-Oct-2013 25-Oct-2013
4 26-Nov-2013 29-Nov-2013



Manish Insan
Software Developer

Manish427, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Allemahesh on: 12/5/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Dear Manish Insan,

I have checked your problem and below query will solve you problem. 

SELECT MIN(tb1.StartDate), MAX(tb2.EndDate) FROM [Table] tb1 INNER JOIN [Table] tb2

ON tb1.id = tb2.id - 1

WHERE tb1.EndDate > tb2.StartDate

UNION 

SELECT MIN(tb2.StartDate), MAX(tb2.EndDate) FROM [Table] tb1 INNER JOIN [Table] tb2

ON tb1.id = tb2.id - 1

WHERE tb1.EndDate < tb2.StartDate


Please let me know if you have any issue?

If this helps you towards the solution, click on MARK IT AS ANSWER

Happy Coding.

Manish427, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Manish427 on: 12/5/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Allemahesh,

This date ranges are not fix, it may be any range, which may overlap each other or may not be.
If any date range is overlapping the other then i need to extract that maximum date range. if none of the date range is overlapping then i want result as it is.
you can verify this is not working for this type of case:
ID StartDate EndDate
1 24-Sep-2013 27-Sep-2013
2 26-Sep-2013 5-Oct-2013
3 7-Oct-2013 10-Oct-2013
4 9-Oct-2013 25-Oct-2013
5 26-Nov-2013 29-Nov-2013


Manish Insan
Software Developer

Manish427, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts