Re: Hotel Booking dates subquery

Jan Hidders wrote:

>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
>
>So which bookings overlap with the period and how can I check this by
>comparing their 'startdate' and 'enddate' with the period in question?
>
>Once you have figured out the SQL for the query above you can quite easily
>use it to build an SQL query that computes the original query. Hint: NOT IN.

I don't mean to sound argumentative, but I'm pretty sure that mine works. Can you give me an example of how mine doesn't work?

I'll have a think about what you've said, although I thought that's what mine did anyway...

Cheers. Received on Sun Nov 10 2002 - 20:05:26 CET