Re: Hotel Booking dates subquery

Scotty wrote:
>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?

From the diagram your query selects:

- booking1
- booking2
- booking3

What it should select is:

- booking2
- booking3
- booking4
- booking5

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

Ok. Btw., any specific reason why you subtract 'sysdate' everywhere?

• Jan Hidders