Re: Hotel Booking dates subquery

Scotty wrote:
>Jan Hidders wrote:
>>Scotty wrote:
>>>Jan Hidders wrote:
>>>>
>>>> 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?
>>>
>>>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 is a set of test dates that actually corresponds with the diagram:

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

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

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

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (4,2,to_date('13-11-02','DD-MM-YY' ),to_date('17-11-02','DD-MM-YY'),100);

insert
into booking (booking_id,room_id,startdate,enddate,duration) values (5,2,to_date('13-11-02','DD-MM-YY' ),to_date('30-11-02','DD-MM-YY'),100);

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

And a query with a slightly bigger period:

select booking_id,room_id,startdate,enddate from booking
where
to_date('10-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('20-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('10-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('20-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

So does it?

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

Do you really need to convert to numbers? The < should already work for dates, but that may depend your DBMS.

>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

What your query actually does is:

nbsd < ebsd and nbed < ebsd
or
nbsd > ebed and nbed > ebed

but that is probably what you meant. Note that this is a bit redundant anyway. If nbsd > ebed then certainly also nbed > ebed because nbed > nbsd. Something similar holds also for the first line.

• Jan Hidders