"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 here:
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
