SQL Scheduling - Overbooked Report

Question

I need a way to view a given resource (in this case rooms/beds) that are overbooked. Here's my table structure. Sorry about the Hungarian notation:

tblRoom
--RoomID

tblBooking
--BookingID
--BeginDate
--EndDate
--AssignedRoomID (foreign key)

I don't have any non-working SQL to post here because I really don't know where to start. I'm using MS Access but I'm looking for a database agnostic solution if possible. It's OK to have to have to change some of the keywords to match the dialect of a given SQL engine but I'd like avoid using other features that are proprietary or only available in one RDBMS.

I realize that it's best to avoid overbooking from the beginning but that's not the point of this question.

In case it's helpful, I posted a related question a couple days ago about how to find resources that are not yet booked for a given data range. You can see that question here.

Edit1:
In reply to the answer below, I've modified your SQL slightly to make it work in Access as well as to be more accurate when it comes to detecting conflicts. If I err not your solution posted below allows some conflicts to go unnoticed but also shows conflicts when a given Booking's EndDate and a different Booking's BeginDate fall on the same day, which is actually allowable and should not show as a conflict. Am I understanding this correctly or am I missing something here?

SELECT
  *
FROM
  tblBooking AS booking
INNER JOIN
  tblBooking AS conflict
    ON  [conflict].AssignedRoomID = [booking].AssignedRoomID
    AND (([conflict].BeginDate >= DateAdd("d", -1, [booking].BeginDate) AND [conflict].BeginDate < [booking].EndDate)
    OR  ([conflict].EndDate > [booking].BeginDate AND [conflict].EndDate < [booking].EndDate))
    AND [conflict].BookingID <> [booking].BookingID

You've made some changes that break the logic. You check if the Begin or End of a conflict happen during the booking, but what if the conflict starts before the booking, and ends after? The only modification you should need to my example code is changing `>=` to `>`, and `<=` to `<`. — MatBailie, May 27 '11 at 08:25
After changing the <= and >= in my first example, could you show some cases that get shown when they should not be, and some cases that don't get shown when they should be? — MatBailie, May 27 '11 at 08:27

MatBailie · Answer 1 · 2011-05-26T13:57:49.940

So, what you're looking for is any record in tblBooking for which there is another record with the same AssignRoomID for an overlapping period?

A naive solution would be...

SELECT
  *
FROM
  tblBooking  [booking]
INNER JOIN
  tblBooking  [conflict]
    ON  [conflict].AssignedRoomID = [booking].AssignedRoomID
    AND [conflict].BeginDate     <= [booking].EndDate
    AND [conflict].EndDate       >= [booking].BeginDate
    AND [conflict].BookingID     != [booking].BookingID

The last condition stops a booking from being it's own conflict. It can also be changed to AND [conflict].BookingID > [booking].BookingID so that you don't get the conflict repeating. (If A conflicts with B, you only get A,B and not B,A.)

EDIT

The issue with the above solution is that it does not scale very well. When searching for a Conflict, all bookings for that room Before the booking's EndDate are found, then filtered based on the EndDate. After a few years, that first search (hopefully using an Index) will return many, many records.

One optimisation is to have a maximum booking length, and only look that many days back in time for a conflict...

INNER JOIN
  tblBooking  [conflict]
    ON  [conflict].AssignedRoomID = [booking].AssignedRoomID
    AND [conflict].BeginDate     <= [booking].EndDate
    AND [conflict].BeginDate     >= [booking].BeginDate - 7     -- Or however long the max booking length is
    AND [conflict].EndDate       >= [booking].BeginDate
    AND [conflict].BookingID     != [booking].BookingID

By having wrapped a >= AND a <= around the [conflict].BeginDate, an index search can now quickly return a reasonably limitted number of records.

For bookings longer than the maximum booking length, they can be entered into the database as multiple bookings. That's where the art of optimisation comes in, it's often all about trade-offs and compromises :)

EDIT

Another option, giving different details, would be to join the bookings against a calendar table. (Having, for example, one record per day.)

SELECT
  [room].RoomID,
  [calendar].Date,
  COUNT(*)                      AS [total_bookings],
  MIN([booking].BookingID)      AS [min_booking_id],
  MAX([booking].BookingID)      AS [max_booking_id]
FROM
  [calendar]
CROSS JOIN
  tblRoom     [room]
INNER JOIN
  tblBooking  [booking]
    ON  [booking].AssignedRoomID = [room].RoomID
    AND [booking].BeginDate     <= [calendar].Date
    AND [booking].EndDate       >= [calendar].Date
GROUP BY
  [room].RoomID,
  [calendar].Date
HAVING
  COUNT(*) > 1

I'm testing your answer, specifically the first one so that I can make sure it works as expected before I start with your other ideas. I'm having some problems because a booking begin data is allowed to overlap another bookings end date. — HK1, May 26 '11 at 19:28
That should be simple enough. Changing the `<=` and `>=` to just `<` and `>` should do it. — MatBailie, May 26 '11 at 22:04

SQL Scheduling - Overbooked Report

1 Answers1

Linked