sql-server - 在 SQL 中检查日期范围

Question

根据我昨天提出的问题，我需要为笔记本电脑推出“预订系统”返回一系列“可用”日期。我想通过检查每个日期可能的空档总数并减去已预订的空档数来填充用户可以预订空档的可能可用日期表。

逻辑如下:

• 一名技术人员每天可以制造 3 台笔记本电脑。
• 任何一天都可能有 1、2 或 3 名技术人员可用。
• 一个表保存所做的预订
• 我不想要所有可能日期的表格，我想即时计算它

相关表格有:

tl_sb_slotBooking这包含已经进行的预订

tl_sb_availabilityPeriods这用于计算给定日期的可用插槽总数

我可以带回具有固定最大槽数(在本例中为 3)的日期列表:

    DECLARE @startDate DATE
    DECLARE @endDate DATE

    SET @startDate = GETDATE()
    SET @endDate = DATEADD(m,3,@startDate)
    ;
    WITH dates(Date) AS 
    (
       SELECT @startdate as Date
       UNION ALL
       SELECT DATEADD(d,1,[Date])
         FROM dates 
         WHERE DATE < @enddate
    )

    SELECT Date
      FROM dates 
    EXCEPT
    SELECT date
    FROM tl_sb_booking
    GROUP BY date
    HAVING COUNT(date) >= 3

但是，最大值并不总是 3，它每天都在变化。

我可以找到给定日期的最大可能时段:

    DECLARE @myDate DATETIME = '2013-06-22'

    SELECT SUM(laptopsPerDay) AS totalSlots
       FROM tl_sb_technicianAvailability
       WHERE startDate <= @myDate AND endDate >= @myDate
       AND availabiltyStateID=3

它将带回 6 作为 2013-06-22 可用的插槽总数。 (availabilityStateID字段用于存储可用/不可用等)

所以，我坚持的一点是将两者结合起来。

我想要的是对于每个可能的日期，如果已经预订的空位数量少于当天可能的空位数量，则将其添加到要返回的表中(否则不要)。

Answer 1

首先，尽管您只生成了一个小列表，using a CTE to generate a sequential list performs terribly最好避免。

为此，我将使用系统表 Master..spt_values 作为数字的顺序列表，但如果您担心使用未记录的系统表，那么链接中还有其他方法以上。

我要做的第一件事是将技术人员的可用日期分成每天一行，这将允许技术人员仅在所需的部分时间内可用(例如，如果您想查询，请从表格的屏幕截图中查看从 6 月 18 日到 6 月 26 日，使用您发布的查询，没有任何技术人员显示为可用):

SELECT  Date = DATEADD(DAY, spt.Number, ta.StartDate),
        ta.TechnicianID,
        ta.LapTopsPerDay
FROM    tl_sb_technicianAvailability ta
        INNER JOIN Master..spt_values spt
            ON spt.Type = 'P'
            AND spt.Number BETWEEN 0 AND DATEDIFF(DAY, ta.startDate, ta.EndDate)

This would simply turn:

TechnicianID    StartDate   EndDate     LapTopsPerDay
1               20130620    20130624    3

进入

Date        TechnicianID    LapTopsPerDay
20130620    1               3
20130621    1               3
20130622    1               3
20130623    1               3
20130624    1               3

然后您可以将此列表限制在所需的日期范围内，并汇总笔记本电脑总数，因为这在技术层面上不需要这样做:

WITH ExplodedAvailability AS
(       SELECT  Date = DATEADD(DAY, spt.Number, ta.StartDate),
                ta.TechnicianID,
                ta.LapTopsPerDay
        FROM    tl_sb_technicianAvailability ta
                INNER JOIN Master..spt_values spt
                    ON spt.Type = 'P'
                    AND spt.Number BETWEEN 0 AND DATEDIFF(DAY, ta.startDate, ta.EndDate)
)
SELECT  Date, TotalLaptops = SUM(LapTopsPerDay)
FROM    ExplodedAvailability
WHERE   Date >= @StartDate
AND     Date < @EndDate
GROUP BY Date;

最后您可以通过 LEFT JOIN 进入预订表以获取每天的可用时段

WITH ExplodedAvailability AS
(       SELECT  Date = DATEADD(DAY, spt.Number, ta.StartDate),
                ta.TechnicianID,
                ta.LapTopsPerDay
        FROM    tl_sb_technicianAvailability ta
                INNER JOIN Master..spt_values spt
                    ON spt.Type = 'P'
                    AND spt.Number BETWEEN 0 AND DATEDIFF(DAY, ta.startDate, ta.EndDate)
), Availability AS
(   SELECT  Date, TotalLaptops = SUM(LapTopsPerDay)
    FROM    ExplodedAvailability
    WHERE   Date >= @StartDate
    AND     Date < @EndDate
    GROUP BY Date
), Bookings AS
(   SELECT  Date, SlotsBooked = COUNT(*)
    FROM    tl_sb_booking
    GROUP BY Date
)
SELECT  Availability.Date,
        Availability.TotalLaptops,
        RemainingSlots = Availability.TotalLaptops - ISNULL(Bookings.SlotsBooked, 0)
FROM    Availability
        LEFT JOIN Bookings
            ON Bookings.Date = Availability.Date;

我认为您想要的是将预订添加到下一个可用的日期，因此执行此操作的查询将是:

DECLARE @UserID INT = 1;

WITH ExplodedAvailability AS
(       SELECT  Date = DATEADD(DAY, spt.Number, ta.StartDate),
                ta.TechnicianID,
                ta.LapTopsPerDay
        FROM    tl_sb_technicianAvailability ta
                INNER JOIN Master..spt_values spt
                    ON spt.Type = 'P'
                    AND spt.Number BETWEEN 0 AND DATEDIFF(DAY, ta.startDate, ta.EndDate)
), Availability AS
(   SELECT  Date, TotalLaptops = SUM(LapTopsPerDay)
    FROM    ExplodedAvailability
    WHERE   Date >= CAST(GETDATE() AS DATE)
    GROUP BY Date
), Bookings AS
(   SELECT  Date, SlotsBooked = COUNT(*)
    FROM    tl_sb_booking
    GROUP BY Date
)
INSERT tl_sb_slotBooking (UserID, Date)
SELECT  @UserID, MIN(Availability.Date)
FROM    Availability
        LEFT JOIN Bookings
            ON Bookings.Date = Availability.Date
WHERE   Availability.TotalLaptops > ISNULL(Bookings.SlotsBooked, 0)