Re: Hotel Booking dates subquery

Jan Hidders wrote:
>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

In my test dates it doesn't. It catches all types of overlap.

here:

create table booking(
booking_id number(4) primary key,
room_id number(20),
startdate date,
enddate date,
duration number(30) -- ignore duration
);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (1,1,to_date('01-11-02','DD-MM-YY' ),to_date('02-11-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (2,1,to_date('02-12-02','DD-MM-YY' ),to_date('04-12-03','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (3,2,to_date('01-12-02','DD-MM-YY' ),to_date('04-12-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (4,2,to_date('05-12-02','DD-MM-YY' ),to_date('07-12-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (5,2,to_date('01-01-03','DD-MM-YY' ),to_date('15-01-03','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (6,3,to_date('11-12-02','DD-MM-YY' ),to_date('29-12-02','DD-MM-YY'),100);

Then use this query (swapping the numeric date for the customers query date)

select booking_id,room_id,startdate,enddate from booking
where
to_date('01-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('02-11-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('01-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('02-11-02','dd-mm-yy')-to_date(sysdate,'dd-mm-yy')> to_date(enddate,'dd-mm-yy')-to_date(sysdate,'dd-mm-yy');

>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?

What i'm doing is subtracting the sysdate from all the dates, leaving me with a number for :
new booking start date - nbsd
new booking end date - nbed
existing booking start date - ebsd
existing booking end date - ebed

which I then compare if the
nbsd < ebsd and nbed < ebed
or the
nbsd > ebsd and nbed > ebed

Ive tested it in several ways and havent made it fall over yet, but i'm guessing you know this field far better than myself, so the moneys on me being wrong...again :-) Received on Sun Nov 10 2002 - 21:24:12 CET