SQL计划-超量预订报告 [英] SQL Scheduling - Overbooked Report

问题描述

我需要一种方法来查看已超额预订的给定资源(在本例中为房间/床).这是我的表结构.抱歉，匈牙利符号:

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

tblRoom
--RoomID

tblBooking
--BookingID
--BeginDate
--EndDate
--AssignedRoomID(外键)

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

我没有任何无法使用的SQL可以在这里发布，因为我真的不知道从哪里开始.我正在使用MS Access，但如果可能的话，我正在寻找与数据库无关的解决方案.不必更改某些关键字以匹配给定SQL引擎的方言是可以的，但我想避免使用专有的或仅在一个RDBMS中可用的其他功能.

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.

为了回答以下答案，我对您的SQL进行了一些修改，使其可以在Access中使用，并且在检测冲突时更加准确.如果我没有弄错，下面发布的解决方案将使一些冲突不被注意到，但是当给定的预订的EndDate和另一个预订的BeginDate落在同一天时显示冲突，这实际上是允许的，不应显示为冲突.我是否正确理解了这一点，或者我在这里错过了什么?

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

推荐答案

因此，您要查找的是tblBooking中的任何记录，在该记录中有另一个记录具有相同的AssignRoomID，并且记录时间重叠?

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

最后一个条件阻止预订成为其自身的冲突.也可以将其更改为AND [conflict].BookingID > [booking].BookingID，这样就不会重复发生冲突. (如果A与B冲突，则只会得到A,B而不是B,A.)

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.)

编辑

上述解决方案的问题在于它的伸缩性不是很好.搜索冲突时，将找到该房间的所有预订，而不是预订的EndDate，然后根据EndDate进行过滤.几年后，第一次搜索(希望使用索引)将返回很多记录.

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

通过将>=和<=包裹在[conflict].BeginDate周围，索引搜索现在可以快速返回合理限制的记录数.

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 :)

编辑

提供不同详细信息的另一个选项是将预订加入日历表. (例如，每天有一条记录.)

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

这篇关于SQL计划-超量预订报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！