sql-server - 按时间查找可用性

Question

我有三张 table 。

表(t)，我系统中的一个资源。
它具有最大容量。

预订 (b)，在
系统，它有 FromDatimeTime，
ToDateTime 和 nrOfPeople

BookedTable (bt)，连接
用 table 和多少座位预订
此预订使用的，a
预订可以有多张 table 。

问题是，如果我有三个预订:

4 人在 12:00 和
14:00出发。

4 人在 12:00 和
13:00出发。

4 人在 13:00 和
14:00出发。

所有预订都使用同一张 table ，有 8 个座位。
我想看看 table 是否“超额预订”。

设置代码

CREATE TABLE Tables (
        TableNr INT, 
        Seats INT
        )
GO 
CREATE TABLE Booking 
    ( 
    BookingNr INT, 
    Time_From DATETIME, 
    Time_TO DATETIME, 
    Guests INT
    )
GO 
CREATE TABLE Table_Booking      
(
 TableBookingId INT ,
 BookingNr INT ,
 TableNr INT ,
 GuestOnTable int 
)
GO 
INSERT INTO [Tables] (  [TableNr],  [Seats]) VALUES ( 1, 8 ) 
INSERT INTO [Tables] (  [TableNr],  [Seats]) VALUES ( 2, 4 ) 
INSERT INTO [Tables] (  [TableNr],  [Seats]) VALUES ( 3, 4 ) 

INSERT INTO [Booking] ([BookingNr],[Time_From],[Time_TO],[Guests]) VALUES ( /* BookingNr - INT */ 1,/* Time_From - DATETIME */ '2009-7-7 11:00',/* Time_TO - DATETIME */ '2009-7-7 13:00',/* Guests - INT */ 4 ) 
INSERT INTO [Booking] ([BookingNr],[Time_From],[Time_TO],[Guests]) VALUES ( /* BookingNr - INT */ 2,/* Time_From - DATETIME */ '2009-7-7 11:00',/* Time_TO - DATETIME */ '2009-7-7 12:00',/* Guests - INT */ 4 ) 
INSERT INTO [Booking] ([BookingNr],[Time_From],[Time_TO],[Guests]) VALUES ( /* BookingNr - INT */ 3,/* Time_From - DATETIME */ '2009-7-7 12:00',/* Time_TO - DATETIME */ '2009-7-7 13:00',/* Guests - INT */ 4 ) 


INSERT INTO [Table_Booking] ([TableBookingId],[BookingNr],[TableNr], GuestOnTable) VALUES (/* TableBookingId - INT */ 1,    /* BookingNr - INT */ 1,/* TableNr - INT */ 1, 4 ) 
INSERT INTO [Table_Booking] ([TableBookingId],[BookingNr],[TableNr], GuestOnTable) VALUES (/* TableBookingId - INT */ 2,    /* BookingNr - INT */ 2,/* TableNr - INT */ 1, 4 ) 
INSERT INTO [Table_Booking] ([TableBookingId],[BookingNr],[TableNr], GuestOnTable) VALUES (/* TableBookingId - INT */ 3,    /* BookingNr - INT */ 3,/* TableNr - INT */ 1, 4 ) 

GO 

简单测试查询

select Booking.BookingNr, [Booking].[Time_From], [Booking].[Time_TO], [Booking].[Guests], [Tables].TableNr  ,  
    CASE WHEN [Tables].[Seats] - 
    (  select sum(tbInner.[GuestOnTable]) from [Table_Booking] as tbInner    
        join [Booking] AS bInner on bInner.BookingNr = tbInner.BookingNr
        where (NOT ( Booking.Time_From>= bInner.[Time_To] OR bInner.[Time_From] >= Booking.Time_To ) )
         ) < 0 THEN 'OverBooked' ELSE 'Ok' END AS TableStatus
        from [Booking] 
        join [Table_Booking] on [Booking].[BookingNr] = [Table_Booking].[BookingNr]
        join [Tables] on [Tables].[TableNr] = [Table_Booking].[TableNr]

给我:
Bookingnr 1 = 超额预订
预订 2 和 3 = 好的。

如果我删除预订 3。我得到了预期的结果。问题是当 3 个或更多预订在同一张 table 上共享时间时。

我不想诉诸诸如在预订期间对所有可能的时间进行循环并检查是否超额预订之类的方法。
该查询经常使用，可能所有用户每分钟一次，一天可能有几百行。
我可以使用 SQL 或 delphi 数据集(但我宁愿在 SQL 中使用)

编辑 1

它是一个更大的存储过程的一部分，它可以做所有其他事情，所以它可以是一个函数。

我宁愿我不必处理最小间隔。
大多数使用该产品的客户都没有这个问题，因为他们不允许拆分表格。而且我不希望查询对他们来说明显变慢。

Answer 1

诀窍是在子查询/派生/CTE 中扩展您的“预订”表，因此每个“间隔”(小时/半小时/分钟/等)是一行，然后您可以 SUM - GROUP BY - HAVING SUM ()> 限制。您将使用 Numbers 表来展开 bookings 表。

使用代码编辑您的问题以构建表格，例如:

DECLARE @t table (col1......   )
INSERT into @t values (......

DECLARE @Booking  table(....
INSERT into @Bookings) values (.....

所以我可以尝试一两个查询，然后我会尝试为您编写...

编辑

要在下面使用我的查询，您需要创建此表:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

这里有一些查询:

--get one row per time "interval" (hour) per booking
SELECT
    b.BookingNr, b.Time_From, b.Time_TO, b.Guests
        ,DATEADD(hh,Number-1,b.Time_From) AS ActualTime
    FROM Booking            b
        INNER JOIN Numbers  n ON DATEADD(hh,Number-1,b.Time_From)<=Time_TO
    ORDER BY 1,5

--get one row per time "interval" (hour), combining each interval
SELECT
    bt.TableNr, SUM(b.Guests) AS TotalGuests
        ,DATEADD(hh,Number-1,b.Time_From) AS ActualTime
    FROM Booking                   b
        INNER JOIN Numbers         n  ON DATEADD(hh,Number-1,b.Time_From)<=Time_TO
        INNER JOIN Table_Booking   bt ON b.BookingNr=bt.BookingNr
    GROUP BY bt.TableNr, DATEADD(hh,Number-1,b.Time_From)
    ORDER BY 3

--get one row per time "interval" (hour), where the seat limit was exceeded
SELECT
    bt.TableNr, SUM(b.Guests) AS TotalGuests
        ,DATEADD(hh,Number-1,b.Time_From) AS ActualTime
        ,t.Seats-SUM(b.Guests) AS SeatsAvailable
    FROM Booking                   b
        INNER JOIN Numbers         n  ON DATEADD(hh,Number-1,b.Time_From)<=Time_TO
        INNER JOIN Table_Booking   bt ON b.BookingNr=bt.BookingNr
        INNER JOIN Tables          t  ON bt.TableNr=t.TableNr
    GROUP BY bt.TableNr,t.Seats, DATEADD(hh,Number-1,b.Time_From)
    HAVING SUM(b.Guests)>t.Seats
    ORDER BY 3

我不确定当超过座位限制时你想如何处理。如果这些查询还不够，请告诉我需要什么...另外，您使用的是什么版本的 SQL Server？