SQL调度 - 超额预订报告

Question

我需要一种方法来查看超额预定的给定资源（在本例中为客房/床）。这是我的表格结构。很抱歉的匈牙利命名法：

tblRoom
--RoomID

tblBooking
--BookingID
--BeginDate
--EndDate
--AssignedRoomID（外键）

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

我意识到最好避免从一开始就超量预订，但这不是这个问题的要点。

如果有帮助，几天前我发布了一个相关问题，关于如何查找尚未针对给定数据范围预订的资源。你可以看到这个问题here。

EDIT1：
在回答下面的答案，我已经修改了你的SQL略，使其在访问工作，以及当它涉及到的检测冲突的更准确。如果我错误地发现您的解决方案不允许您发现一些冲突，但是当给定的预订结束日期和不同的预订开始日期同一天发生冲突时，也会显示冲突，这实际上是允许的，并且不应该表现为冲突。我是否正确理解这一点，或者我在这里错过了什么？

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 

Answer 1

那么，你要找的是tblBooking中的任何记录，其中有一个重叠期间的AssignRoomID相同的另一条记录？

一个天真的解决办法是......

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。）

编辑

与上述解决方案的问题是，它没有很好地进行缩放。当搜索冲突时，该房间的所有预订都会在预订的结束日期之前找到，然后根据EndDate进行过滤。几年后，首次搜索（希望使用索引）将返回许多许多记录。

一种优化是有一个最大长度预约，并且只能看多天回来的时间冲突...

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周围<=，索引搜索现在可以快速返回合理限制的记录数。

对于长于最大预订长度的预订，可以将它们作为多个预订输入到数据库中。这就是最优化的技术进来，它往往是所有关于权衡和妥协:)

编辑

另一种选择，让不同的细节，将加入预约对日历表。 （例如，每天有一条记录。）

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