score:1

Accepted answer

This query will find bookings that overlap the specified arrival and departure date. It assumes departure date is exclusive (i.e. guest can arrive on the day the previous guest departs):

SELECT *
FROM Bookings
WHERE RoomId = 1 AND @DepartureDate > ArrivalDate AND DepartureDate > @ArrivalDate
-- @Arrival/Departure dates are the ones you want to check

All of your three tests here

Given this query it is trivial to find available rooms.

score:1

If you want other bookings at the same time:

Select b.*
From Bookings
Where RoomId = 1 And 
      ArrivalDate <= '2018-09-21' and
      DepartureDate >= '2018-09-20';

The logic is simple. Two periods overlap if one starts on or before the second ends. And, the first ends on or after the second starts. This assumes the periods are inclusive of the end points. You can adjust the <= (to <) and >= (to >) if they are exclusive.

If this returns no records, then the period (2018-09-20 - 2018-09-21) is free for a new booking.


Related Query

More Query from same tag