Re: Hotel Booking dates subquery

Scotty,

  Sean gave you the best hint. Suppose you have a table

booking (booking_id,startdate,enddate)

and someone wants to check in on :start and leave on :end.

They can do that if there is no overlapping booking. A new booking from :start to :end is compatible with one existing booking from startdate to enddate so long as the new booking is either fully before the existing one or fully after. The new booking is fine, then, if

:end <= startdate or :start >= enddate

That should be easy to see - if the person will check out on or before the start date of the existing booking, everything is fine, as it also is if the person will check in on or after the end of the existing booking.

The new booking is impossible, then, if this is not true, i.e. if :end <= startdate or :start >= enddate is false. A condition of the form X OR Y is false if X is false and Y is false. So there is a conflict exactly when

  :end > startdate and :start < enddate

Considering all existing bookings, the new booking is fine if there is no conflict, or if

not exists (
  select * from booking
  where :end > startdate and :start < enddate )

For whatever reason, the condition for intervals _not_ overlapping is much easier to conceptualize than the condition for intervals overlapping: "Yes, their lives overlapped, because he died after she was born, and he was born before she died." True, but not obvious.

Steve Kass
Drew University

Scotty wrote:

>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 Sun Nov 10 2002 - 20:32:54 CST