代码之家 › 专栏 › 技术社区 › Richard L

按时间查找可用性

datetime delphi sql-server

Richard L · 技术社区 · 17 年前

我有三张桌子。

表(t),我系统中的一个资源。
预订(b),在 ToDateTime和nrOfPeople
BookedTable(bt),连接预订可以有多张桌子。

4人在12:00和
4人在12:00和
下午2点离开。

所有预订均使用8个座位的同一张桌子。我想看看这张桌子是否“超订”了。

设置代码

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]

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

我不想在预订过程中反复查看所有可能的时间,并检查是否超订。该查询经常被所有用户使用,可能每分钟一次,一天可能会有几百行。

编辑1

它是一个更大的存储过程的一部分,该过程执行所有其他类型的任务,因此它可能是一个函数。

大多数使用该产品的客户都没有这个问题,因为他们不允许拆分桌子。我不希望他们的查询速度明显变慢。

1 回复 | 直到 17 年前

KM. 17 年前

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

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