在 SQL 中检查日期范围 [英] Checking a range of dates in SQL
问题描述
继我昨天提出的一个问题之后，我需要为笔记本电脑推出预订系统"返回一系列可用"日期.我想通过检查每个日期的总可能空位数量并减去已预订的空位数量来填充用户可以预订空位的可能可用日期表.
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 中检查日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
