How to query for overlapping dates

[DavidZ]

Hi All,

I am trying to find all Payees who have overlapping dates. It would look something like this:

PayeeID: 3245


1/1/2011 - 5/1/2014
4/1/2013 - 4/1/2014
4/2/2014 - 9/1/2014

In this example, 5/1/2014 is incorrect and should be 3/31/2013.

My SQL is as follows, but isn't returning anyone.

SELECT EffectivePayee1.PayeeID
FROM EffectivePayee1
WHERE (((EffectivePayee1.Start_Date)>[EffectivePayee1].[End_Date] And (EffectivePayee1.Start_Date)<[EffectivePayee1].[End_Date]))

Thoughts?

[orange]

See this info from PBaldy's site

[DavidZ]

Thanks, orange!

I changed it around and it worked. It is surprisingly hard to wrap my mind around why it works and the other way doesn't, though....

The SQL that works is as follows:

SELECT EffectivePayee1.PayeeID
FROM EffectivePayee1
WHERE (((EffectivePayee1.Start_Date)>[EffectivePayee1].[End_Date] And (EffectivePayee1.End_Date)<[EffectivePayee1].[Start_Date]));

[John_G]

Are you sure that is doing what you think it does? If in your original post those are three different records for that employee, then the solution to your question becomes much more difficult.

The SQL can only compare dates on one record at a time, not between records. And if you exclude equal values for A and B, then if A > B, obviously B < A - this is what your revised SQL is doing.

[DavidZ]

Hi John_G,

No, I'm not sure that it is doing what I want it to do. The reason I initially thought this was the answer is simply because I returned records (versus not returning any the first time around).

However, I was expecting to return a lot more than I did and I think this is because of what you mention above.

Is there any way to compare dates between records?

[orange]

David,

Take a look at this sample from the forum code repository. It may give you some ideas.

[DavidZ]

Whoops, hold on real quick.

[DavidZ]

Ok, this is how I got it to work in the end:

SELECT DISTINCT EffectivePayee1.PayeeID
FROM EffectivePayee1 INNER JOIN EffectivePayee2 ON EffectivePayee1.PayeeID=EffectivePayee2.PayeeID
WHERE (EffectivePayee1.Start_Date BETWEEN EffectivePayee2.Start_Date AND EffectivePayee2.End_Date
OR EffectivePayee1.End_Date BETWEEN EffectivePayee2.Start_Date AND EffectivePayee2.End_Date)
AND EffectivePayee1.Start_Date<>EffectivePayee2.Start_ Date
AND EffectivePayee1.End_Date<>EffectivePayee2.End_Date