2

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.

Salman A
  • 262,204
  • 82
  • 430
  • 521
Dane Marshall
  • 41
  • 5

2 Answers2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Gordon, what are you showing here? This query would presumably output Booking 3, suggesting he can't book it for this night when in reality he can? – Derrick Moeller Sep 20 '18 at 19:51
  • @DerrickMoeller . . . This is returning the conflicts for a given period of time. If the query returns no results, then there are no conflicts. – Gordon Linoff Sep 20 '18 at 19:57
  • Your query returns two records, Booking 2 and Booking 3. But the date range September 20th - 21st shouldn't have any conflicts? – Derrick Moeller Sep 20 '18 at 20:00
  • This is for inclusive end dates, OP wants exclusive end dates e.g. if someone departs on 21 Sep then that day should be considered available. – Salman A Sep 20 '18 at 20:27
  • @DerrickMoeller . . . This assumes the end dates are included in the period. You just need to adjust the comparisons if your rules are slightly different. – Gordon Linoff Sep 20 '18 at 23:26
1

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.

Salman A
  • 262,204
  • 82
  • 430
  • 521
  • Thank you for this. This is ALMOST there with another spanner in the works. If I had a booking already booked on 21/09/2018 for 1 night, BUT I wanted to update this booking and add extra dates on to it, the query returns a result, obviously. How would I combat updating to say, same arrival date but depature date is 24/09/2018? – Dane Marshall Sep 20 '18 at 20:39
  • Sorry I don't get it. Are you saying that the system flags the booking conflicting with itself? Then simply add `Other.IDBooking <> ToUpdate.IDBooking`. @Dane – Salman A Sep 20 '18 at 20:45
  • No sorry. I made a booking, let's call this Id=1. But I would like to update this booking to add extra days on to it, instead of having 1 night, I'd like to update it to 3 nights. But instead of creating a new booking, I'd like to update it but still check availability just in case the other dates become unavailable. @Salman A – Dane Marshall Sep 20 '18 at 20:48
  • You will still use the same query to check for conflicts _before_ updating: `SELECT * FROM Bookings WHERE RoomId = 1 AND '24/09/2018' > ArrivalDate AND DepartureDate > '21/09/2018' AND Id <> 1`. Notice that I added `Id <> 1` because a booking will always conflict with itself; you want to check only others. – Salman A Sep 20 '18 at 20:52
  • Thank you very much @Salman, works a treat. Your help is extremely appreciated. – Dane Marshall Sep 20 '18 at 21:07