Find Records that ovelap

I have a table with 2 date fields startdate and enddate. They vaules are long int in yyyymmdd format.

I want to check for any overlap between records.

Eg if record 1 starts on 20040501 and ends on 20040530 and record 2 starts on 20040515 there is overlap between these records.

John H

"John H" wrote
I have a table with 2 date fields startdate and enddate. They vaules are long int
in yyyymmdd format.

I want to check for any overlap between records.

Eg if record 1 starts on 20040501 and ends on 20040530 and record 2 starts on
20040515 there is overlap between these records.

Hi John,

Michel once explained overlap so succinctly,
I quote him he

Ranges do NOT overlap if
( I assume aStart, aEnd, bStart and bEnd are the intervals):

aStart bEnd OR aEnd bStart

they overlap, in part or in full, on the negation of that statement, ie
(Apply De Morgan's law) :

aStart = bEnd AND aEnd = bStart

end quote

If I understand correctly, you want a query that will
look at all the records in the table and find the ones
that overlap.

One possible method might be to bring the table
twice into your query,

set alias of one to "a", the other to "b",

no join (Cartesian join),

bring a.StartDate, a.EndDate, b.StartDate, b.EndDate
down into field rows of grid,

then use above in WHERE clause
(but maybe also add clause to weed out where they
are both equal, maybe you won't need this)

SELECT
a.StartDate,
a.EndDate,
b.StartDate,
b.EndDate
FROM yourtable AS a, yourtable AS b
WHERE
(a.StartDate = b.EndDate
AND
a.EndDate = b.StartDate)
AND
(a.StartDate b.StartDate
AND
a.EndDate b.EndDate);

This is untested, but hopefully this will get
you started if something not quite right.
Of course you can add other fields from
your a and b table.

Good luck,

Gary Walter