Home -> Usenet -> comp.databases.theory -> Re: Hotel Booking dates subquery

Re: Hotel Booking dates subquery

From: Steve Kass <skass_at_drew.edu>
Date: Sun, 10 Nov 2002 21:32:54 -0500
Message-ID: <aqn4p2$al0$1_at_slb6.atl.mindspring.net> 







  
  


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 Mon Nov 11 2002 - 03:32:54 CET












Original text of this message