Re: Hotel Booking dates subquery

Scotty wrote:
>Scotty wrote:
>
>[blah...blah...blah]
>
>I think ive got it... Need to run some more tests, but...
>
>SELECT booking_id,room_id,startdate,enddate
>FROM booking
>WHERE
>to_date('29-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy') <
>to_date(startdate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy')
>and
>to_date('05-12-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy') <
>to_date(startdate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy')
>or
>to_date('29-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy') >
>to_date(enddate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy')
>and
>to_date('05-12-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy') >
>to_date(enddate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy');
>
>Can someone with a brain left (mines committed suicide) tell me if I'm
>on the right track?

Not really. Do not despair, let me try to get the nose of your little steam engine in the right direction. The main trick in this case is to first try to solve the negation, i.e., the opposite, of the original query you are trying to write down. So that would be here:

  "Give me all the rooms that are booked on at least one date between 29-11    and 5-12."

So how do we compute this query? Well, we look at every booking and check if the period overlaps with the period from 29-11 through 5-12. So when exatly does a period from 'startdate' to 'enddate' overlap with this period? You should be able to figure this out for yourself but if you have problems with this consider the following schematical diagram of all possible situations:

  period:                 29-11 ------------ 5-12

  booking1:  start -- end
  booking2:  start --------------------- end
  booking3:  start ---------------------------------------- end
  booking4:                     start -- end
  booking5:                     start --------------------- end
  booking6:                                        start -- end