Re: Hotel Booking dates subquery

Steve Kass wrote:
>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.

Thanks Steve, that was an excellent explanation of what's going on.

And thanks to everyone else who's tried to point me in the right direction with this problem! Received on Mon Nov 11 2002 - 09:00:32 CET