REPOST: Re: How to search date ranges
Date: Sun, 27 Jan 2002 22:37:50 GMT
Message-ID: <1$--$%%%_$$$%$-$$$_at_news.noc.cabal.int>
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
- WAS CANCELLED BY =======: From: j.w.vandijk_at_hetnet.nl (Jaap W. van Dijk) Control: cancel <3c547f4f.2248076_at_news.freeler.nl> Subject: cmsg cancel <3c547f4f.2248076_at_news.freeler.nl> Date: Mon, 28 Jan 2002 01:51:10 GMT Message-ID: <cancel.3c547f4f.2248076_at_news.freeler.nl> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!cert.uni-stuttgart.de!news.belwue.de!newsfeed.arcor-online.net!newsfeed01.sul.t-online.de!newsfeed00.sul.t-online.de!t-online.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!out.nntp.be!propagator-SanJose!in.nntp.be!news-in-sanjose!sjc-feed.news.verio.net!sea-feed.news.verio.net!news.verio.net!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40720150
This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Sun Jan 27 2002 - 23:37:50 CET