Re: Hotel Booking dates subquery

Here's the "template" query. For additional explanation see Jan's post. It works correct for dates in the past time, and in order to make it correct for dates in future time you should change either the query or the model you adopted (the way you represent 'eternity' end_date).

SELECT room_id
FROM rooms
WHERE room_id NOT IN (

    SELECT DISTINCT room_id
    FROM bookings
    WHERE room_status='occupied' and

          (('29-11-02'>start_date and '29-11-02'<end_date) or
          ('05-12-02'>start_date and '05-12-02'<end_date) or
          ('29-11-02'<start_date and '05-12-02'>end_date))

Best wishes,
Damjan S. Vujnovic

University in Belgrade
Faculty of Electrical Engineering
Department for Computer Architecture and Informatics

http://galeb.etf.bg.ac.yu/~damjan

"Scotty" <invallid_at_invalid.spam> wrote in message news:2kmssu0oq18l60fc1srtbf6rp7e3euobve_at_4ax.com...
> 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?
Received on Mon Nov 11 2002 - 05:54:02 CET