問(wèn)題描述

繼我昨天提出的一個(gè)問(wèn)題之后，我需要為筆記本電腦推出預(yù)訂系統(tǒng)"返回一系列可用"日期.我想通過(guò)檢查每個(gè)日期的總可能空位數(shù)量并減去已預(yù)訂的空位數(shù)量來(lái)填充用戶可以預(yù)訂空位的可能可用日期表.

Following on from a question I put on yesterday, I need to return a range of "available" dates for a laptop rollout "booking system". I want to populate a table of possible available dates a user can book a slot on by checking for each date the total possible number of slots, and subtracting the number of slots already booked.

邏輯如下:

• 一名技術(shù)人員每天可以制造 3 臺(tái)筆記本電腦.
• 任何一天都可能有 1、2 或 3 名技術(shù)人員可用.
• 一張桌子上放著預(yù)訂的東西
• 我不想要包含所有可能日期的表格，我想即時(shí)計(jì)算它

相關(guān)表格有:

tl_sb_slotBooking這包含已經(jīng)進(jìn)行的預(yù)訂

tl_sb_availabilityPeriods這用于計(jì)算給定日期的可用插槽總數(shù)

tl_sb_availabilityPeriods This is used to calculate the total number of available slots on a given day

我可以帶回具有固定最大插槽數(shù)(在本例中為 3)的日期列表:

I can bring back a list of dates with a fixed maximum number (in this case 3) of slots:

    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，它每天都在變化.

However, the maximum won't always be 3, it changes for each day.

我可以找到給定日期的最大可能時(shí)段:

I can find the maximum possible slots for a given day:

    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 可用的插槽總數(shù).(availabilityStateID 字段用于存儲(chǔ)可用/不可用等)

it will bring back 6 as the total number of slots available for 2013-06-22. (The availabilityStateID field is used to store available/unavailable etc.)

所以，我堅(jiān)持的一點(diǎn)是將兩者結(jié)合起來(lái).

So, the bit I am stuck on is combining the two.

我想要的是每個(gè)可能的日期，如果已經(jīng)預(yù)訂的空位數(shù)量少于當(dāng)天可能的空位數(shù)量，則將其添加到返回的表中(否則不要).

What I want is for each possible date, if the number of slots already booked is less than the number of possible slots for that day, add it to the table being returned (otherwise don't).

推薦答案

首先，雖然你只是生成了一個(gè)小列表，使用 CTE 生成順序列表的性能非常糟糕，最好避免.

Firstly, althought you are only generating a small list, using a CTE to generate a sequential list performs terribly and is best avoided.

為此，我將使用系統(tǒng)表 Master..spt_values 作為數(shù)字的順序列表，但如果您擔(dān)心使用未記錄的系統(tǒng)表，那么鏈接中還有其他方法

For the sake of this I will use the system table Master..spt_values for a sequential list of numbers, but if you are worried about using undocumented system tables then there are other methods in the link above.

我要做的第一件事是每天將技術(shù)人員的可用日期分成一行，這將允許技術(shù)人員僅在所需的部分時(shí)間可用(例如，如果您想查詢，則從表格的截圖中從 6 月 18 日到 6 月 26 日，使用您發(fā)布的查詢，所有技術(shù)人員都不會(huì)顯示為可用):

The first thing I would do is split the technician availability dates into a row per day, this will allow for technicians who are available for only part of the peiod required (e.g. from your sceen shot of the table if you wanted to query from 18th June to 26th June none of the technicians show would appear as available using the query you have posted):

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

進(jìn)入

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

然后您可以將此列表限制在所需的日期范圍內(nèi)，并總結(jié)可以完成的筆記本電腦總數(shù)，因?yàn)檫@在技術(shù)層面上是不需要的:

You can then limit this list to the date range required, and sum up the total laptops than can be done as this is not needed on a technicial level:

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 加入預(yù)訂表以獲取每天可用的空位

Finally you can LEFT JOIN to the bookings table to get the available slots per day

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;

我認(rèn)為您想要的是將預(yù)訂添加到下一個(gè)可用日期，因此執(zhí)行此操作的查詢將是:

I think what you are after is to add a booking to the next available day, so the query to do this would be:

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)

這篇關(guān)于在 SQL 中檢查日期范圍的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！