問題描述
繼我昨天提出的一個問題之后，我需要為筆記本電腦推出預訂系統"返回一系列可用"日期.我想通過檢查每個日期的總可能空位數量并減去已預訂的空位數量來填充用戶可以預訂空位的可能可用日期表.
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.
邏輯如下:
- 一名技術人員每天可以制造 3 臺筆記本電腦.
- 任何一天都可能有 1、2 或 3 名技術人員可用.
- 一張桌子上放著預訂的東西
- 我不想要包含所有可能日期的表格，我想即時計算它
相關表格有:
tl_sb_slotBooking這包含已經進行的預訂
tl_sb_availabilityPeriods這用于計算給定日期的可用插槽總數
tl_sb_availabilityPeriods This is used to calculate the total number of available slots on a given day
我可以帶回具有固定最大插槽數(在本例中為 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.
我可以找到給定日期的最大可能時段:
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 可用的插槽總數.(availabilityStateID 字段用于存儲可用/不可用等)
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.)
所以，我堅持的一點是將兩者結合起來.
So, the bit I am stuck on is combining the two.
我想要的是每個可能的日期，如果已經預訂的空位數量少于當天可能的空位數量，則將其添加到返回的表中(否則不要).
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).
推薦答案
首先，雖然你只是生成了一個小列表，使用 CTE 生成順序列表的性能非常糟糕，最好避免.
Firstly, althought you are only generating a small list, using a CTE to generate a sequential list performs terribly and is best avoided.
為此，我將使用系統表 Master..spt_values 作為數字的順序列表，但如果您擔心使用未記錄的系統表，那么鏈接中還有其他方法
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.
我要做的第一件事是每天將技術人員的可用日期分成一行，這將允許技術人員僅在所需的部分時間可用(例如，如果您想查詢，則從表格的截圖中從 6 月 18 日到 6 月 26 日，使用您發布的查詢，所有技術人員都不會顯示為可用):
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
進入
Date TechnicianID LapTopsPerDay
20130620 1 3
20130621 1 3
20130622 1 3
20130623 1 3
20130624 1 3
然后您可以將此列表限制在所需的日期范圍內，并總結可以完成的筆記本電腦總數，因為這在技術層面上是不需要的:
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 加入預訂表以獲取每天可用的空位
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;
我認為您想要的是將預訂添加到下一個可用日期，因此執行此操作的查詢將是:
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)
這篇關于在 SQL 中檢查日期范圍的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！