Re: Help with a query please

"Hugh Bothwell" <hugh_bothwell@hotmail.com> wrote in message news:20030422230921.47842.qmail@pb1.pair.com... > > "Shaun" <shaun@mania.plus.com> wrote in message > news:20030422220705.6222.qmail@pb1.pair.com... > > [snip] > > How can i check whether a user is already booked for a given period? I > > have tried the following query but this only checks for a booking that > > starts before the gaiven date and ends after it > > > > SELECT B.* > > FROM WMS_User U, WMS_Bookings B > > WHERE B.User_ID = '1' > > AND B.Booking_Start_Date >= '2003-04-28' > > AND B.Booking_End_Date <= '2003-04-29' > > > > if a user is booked from 2003-04-29 to 2003-05-01 then the query returns > no > > rows... > > > So what you want to know is if there is any overlap > between an input period and one or more already- > booked periods? > > > Using an existing booked period as reference, > any arbitrary date can be _before, _in, or _after. > Looking at the start and end dates of any arbitrary > period, there are nine possible combinations > (start=caps, end=lower): > > Bb Bi Ba > Ib Ii Ia > Ab Ai Aa > > If we constrain the end date to be on or after the > start date, three combinations are impossible > > Bb Bi Ba > __ Ii Ia > __ __ Aa > > > Now, you can test for periods which DO > > ** Bi Ba > __ Ii Ia > __ __ ** > > or DO NOT > > Bb ** ** > __ ** ** > __ __ Aa > > coincide; it looks simpler to test for > non-coincidence and assume the opposite > if it fails. > > SELECT > b.* > FROM > WMS_User u JOIN WMS_Bookings b ON u.ID = b.User_ID > WHERE > u.ID = $user > AND NOT ( > $end_date < b.Booking_Start_Date > OR > $start_date > b.Booking_End_Date > ) > > This should return all bookings which > collide with your input period. > > Hope this helps... > > Thanks for your help, would this be the same if i have just a single day to check for collision wi th other bookings?