Re: How to search date ranges

Apart from the problem you are having with your query optimization, if you have two intervals a to b and p to q and you want to check for overlap it suffices to check the following condition:

b >= p and a <= q

Assuming that equal values constitute an overlap. If this condition is true, you have overlap, if it's not true you don't have overlap.

Check it out for your examples.

Jaap.

On Sun, 27 Jan 2002 17:52:30 GMT, Michael Gill <mydba_at_usa.com> wrote:

snip
>
>
> O-----------------O
> start stop
> O------O
> O---------O
> O---------O
> O-------------------------------O
>
snip
>
> SELECT Count(s.Person_ID)
> FROM SCHEDULED s, Event e
> WHERE ((e.Start_Time > (TO_DATE('14-nov_01')) AND (e.Start_Time <
>TO_DATE('15-nov_01'))) OR
> (e.Stop_Time > (TO_DATE('14-nov_01')) AND (e.Start_Time <
>TO_DATE('15-nov_01'))) OR
> ((e.Start_Time <= TO_DATE('14-nov_01')) AND (e.Stop_Time >=
>TO_DATE('15-nov_01')))) AND
> (e.Event_ID = s.Event_ID) AND
> (s.Person_ID = 9957)
>
snip Received on Sun Jan 27 2002 - 23:37:50 CET