Finding overlapping dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Finding overlapping dates

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-05-25 : 11:23:20

I have 3 fields -

Contract_id
discount_state_date
discount_end_date

I need to return a result of the listing of Contract_ID's where the dates overlap -

Sample data -

10 4/1/2010 3/31/2011
10 8/1/2009 7/31/2010
10 8/1/2008 7/31/2009

I need to find issues like this where data entry was wrong and dates overlap.

Any help would be appreciate. Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-25 : 12:08:14

One of the way to solve is as follows:

set dateformat mdy
Declare @Mytable Table
(
Contract_id int,
discount_state_date datetime,
discount_end_date datetime )

Insert into @Mytable
Select 10 ,'4/1/2010','3/31/2011' Union
select 10, '8/1/2009' ,'7/31/2010' union
select 10 ,'8/1/2008' ,'7/31/2009'union

Select T1.Contract_id, T1.discount_state_date, T1.discount_end_date,
T2.discount_state_date as OverLappingStartDate, T2.discount_end_date as OverLappingEndDate
from @Mytable T1 join @Mytable T2 on T1.Contract_id = T2.Contract_id
And T2.discount_state_date> T1.discount_state_date and T2.discount_state_date < T1.discount_end_date

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources