Re: Hotel Booking dates subquery

Damjan,

  Good point - OVERLAPS (if available) is probably more readable, and it also handles the use of NULL for end_date to mean a booking from start_date that does not end. That might be unlikely for a hotel reservation perhaps, but
very handy in some situations. As pointed out at http://www.pgro.uk7.net/fp2a.htm,
OVERLAPS should be implemented so that

(ay,dy) OVERLAPS (ax,dx)

is equivalent to

(ay > ax AND (ay < dx OR dy < dx)) OR
(ax > ay AND (ax < dy OR dx < dy)) OR
(ay = ax AND ay IS NOT NULL AND ax IS NOT NULL)

SK

Damjan S. Vujnovic wrote:

>: Hate to bring up the dead, but might this also work?
>:
>: SELECT room_id
>: FROM rooms
>: WHERE room_id NOT in (
>: SELECT distinct room_id
>: FROM bookings
>: WHERE status = 'occupied'
>: AND (start_date,end_date)
>: OVERLAPS(to_date('02/02/03','DD/MM/YY'),
>: to_date('07/02/03','DD/MM/YY')));
>
>Looks good to me...
>
>--
>regards,
>Damjan S. Vujnovic
>
>University of Belgrade
>School of Electrical Engineering
>Department of Computer Engineering & Informatics
>Belgrade, Yugoslavia
>
>http://galeb.etf.bg.ac.yu/~damjan/
>
>
>
>
Received on Mon Dec 02 2002 - 04:37:34 CET