Find bookings that conflict with given arrival / departure dates

user_8833868 0
Viewed 120

I have a table called Bookings which contains the following columns:-

RoomId (int)
ArrivalDate (datetime)
DepartureDate (datetime)

The issue I'm having is checking for bookings already created. Examples below.

Booking 1 - 1 Night
ArrivalDate = 17/09/2018
DepartureDate = 18/09/2018

Booking 2 - 2 Nights
ArrivalDate = 18/09/2018
DepartureDate = 20/09/2018

Booking 3 - 4 Nights
ArrivalDate = 21/09/2018
DepartureDate = 25/09/2018

So this leaves the availability for a 1-night booking from 20/09/2018 to 21/09/2018.

My struggle is finding the correct query to combat the below scenarios.

Booking 4 - 3 Nights
ArrivalDate = 20/09/2018
DepartureDate = 23/09/2018

This would fail because booking 3 has 21/09/2018 and 22/09/2018 already taken.

Booking 5 - 7 Nights
ArrivalDate = 20/09/2018
DepartureDate = 27/09/2018

This would fail because booking 3 has 21/09/2018, 22/09/2018, 23/09/2018 and 24/09/2018 already taken. But, 25/09/2018 and 26/09/2018 are available

I've written a function, albeit it's currently failing, to check if a booking can be made.

var o = await dc.Bookings.Where(c =>
    c.RoomId == roomId && c.IsDeleted == false  &&
    (c.ArrivalDate > arrivalDate && c.DepartureDate < departureDate)).CountAsync();

Select * From Bookings Where RoomId = 1 And
    (ArrivalDate Between '2018-09-20 00:00:00.000' And '2018-09-21 00:00:00.000') Or
    (DepartureDate Between '2018-09-20 00:00:00.000' And '2018-09-21 00:00:00.000') Or
    (ArrivalDate >= '2018-09-20 00:00:00.000' And DepartureDate <= '2018-09-21 00:00:00.000')

I've been at this ALL day and haven't been able to figure it out. Any pointers in the right direction would be greatly appreciated.

date entity-framework sql linq c#
2 Answers

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.

user_11440350

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.

user_870150
Related