Re: Hotel Booking dates subquery

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

Your test data has a big problem in that you have the same room being used on conflicting dates:

BOOKING_ID ROOM_ID STARTDATE ENDDATE DURATION

---------- ---------- --------- --------- ----------
         1          1 01-NOV-02 05-NOV-02        100
         2          1 01-NOV-02 17-NOV-03        100
         3          2 01-NOV-02 30-NOV-02        100
         4          2 13-NOV-02 17-NOV-02        100
         5          2 13-NOV-02 30-NOV-02        100
         6          3 25-NOV-02 30-NOV-02        100 

Room 1 -> two occurances on 01-nov-02 -- impossible Room 2 -> two occurances on 13-nov-02 -- impossible

Youre using corrupt data to test with? Nothing is going to work if you do that.

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

I'm curious to ask whether you think the theory behind my query works or not, given test data that reasonable constraints would allow. Received on Mon Nov 11 2002 - 00:20:26 CET