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-06-30 : 16:42:18

I posted on this before and thought I was in a good spot -

My sql to date is -


SELECT t1.contract_id, t1.effective_start_date, 
1.effective_end_date, t2.effective_start_date, 
t2.effective_end_date
FROM Dim_Contract AS t1 INNER JOIN Dim_Contract AS t2 ON (t1.effective_end_date<>t2.effective_start_date) 
AND (t1.effective_start_date<>t2.effective_start_date) 
AND (t2.effective_start_date between t1.effective_start_date 
and t1.effective_start_date 
or t2.effective_end_date between t1.effective_start_date 
and t1.effective_end_date) AND (t1.contract_id=t2.contract_id);

Which seems to work fine when start dates and end dates are overlapping but it seems to miss entries like the follow -

CONTRACT_ID EFFECTIVE_START_DATE EFFECTIVE_END_DATE
P01811 7/1/2008 6/30/2009
P01811 7/1/2008 4/30/2009

Can anyone help tweak my sql so that I cover all possible date overlapping scenerios? Basically within a contract every entry for dates cannot overlap with another. So in the example that failed that entry cannot happen.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-30 : 16:45:26

[code]SELECT *
FROM dimContract AS c1
INNER JOIN dimContract AS c2 ON c2.Effective_Start_Date <= c1.Effective_End_Date
AND c2.Effective_End_Date >= c1.Effective_Start_Date
WHERE c1.Contract_ID <> c2.Contract_ID[/code]

N 56°04'39.26"
E 12°55'05.63"

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-06-30 : 19:00:19

I ran that query with the following data in a table -

Contract_ID Effective_start_date Effective_end_date
P01551 1/1/2010 12/31/2010
P01551 1/1/2010 6/30/2010
P01551 7/1/2010 12/31/2010
P01881 7/1/2008 6/30/2009
P01881 7/1/2008 4/30/2009

It returned no results.

It should have returned that they all overlapped in some capacity.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-01 : 02:53:44

1) What is the unique key for the records?
2) What datatype are Effective_Start_Date and Effective_End_Date?

N 56°04'39.26"
E 12°55'05.63"

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-07-01 : 10:15:45

There is a Contract_Key that is an auto number that is not shown. That is the unique key.
And the dates are in a date/time format.

I am trying to look at at Contract_ID and where contract_ids are the same the dates cannot overlap at all. I am pulling in a feed and trying to run a query to quickly find the contracts where there are date issues.

Which would be - start dates are the same, end dates are the same, or dates are in between each other (ie - 1/1/2010 - 5/31/2010 and 2/1/2010 and 6/30/2010)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-02 : 04:51:49

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources