我正在建立一个调度系统，以跟踪专业人士的时间表和可用性。用户应该能够输入一些条件，并查看专业人士下一个可用约会时间的最佳匹配专业人员列表。返回这些专业人员的查询工作起作用，但是随着托管专业人员的数量的增加，返回结果所需的时间变得荒谬。
系统工作的3个表工作：专业定义了专业人员， availability 定义了相关专业人员的可用性块， piforment 定义安排了针对相关专业人员的约会。因此，一些示例数据可能看起来像：
INSERT INTO professional (id, name)
VALUES
(1, 'Bob'),
(2, 'Frank'),
(3, 'Joe');
INSERT INTO availability (id, professional_id, start_date_time, end_date_time)
VALUES
(1, 1, '03/06/2017 09:00:00', '03/06/2017 12:30:00'),
(2, 1, '03/06/2017 13:30:00', '03/06/2017 18:00:00'),
(3, 2, '03/06/2017 10:00:00', '03/06/2017 14:00:00'),
(4, 3, '03/07/2017 08:30:00', '03/07/2017 16:30:00');
INSERT INTO appointment (id, professional_id, start_date_time, end_date_time)
VALUES
(1, 1, '03/06/2017 09:00:00', '03/06/2017 09:30:00'),
(2, 1, '03/06/2017 10:00:00', '03/06/2017 10:30:00'),
(3, 2, '03/06/2017 10:00:00', '03/06/2017 10:30:00'),
(4, 2, '03/06/2017 10:30:00', '03/06/2017 11:00:00'),
(5, 2, '03/06/2017 11:00:00', '03/06/2017 11:30:00');
查询应沿着：
的线返回结果集name | next_availability
----- | -----------------
Bob | 03/06/2017 09:30:00
Frank | 03/06/2017 11:30:00
Joe | 03/07/2017 08:30:00
我正在使用在Technet上找到的方法（有一些修改可以与我的实际设置一起使用）来找到下一个可用性，但是就像我说的那样，作为专业人士的数量及其可用性和约会，返回结果所需的时间变得不合理。
，还有其他要求，只是为了使这一点更加困难，结果不应包括任何没有任何可用性的专业人员。
我看到的瓶颈是必须为所有这些专业人员计算下一个可用性。我已经考虑建立一张可用的约会表，但是该表的维护将是令人望而却步的。关于如何有效地使这项工作有什么想法？
预先感谢您的帮助。
编辑为包括工作查询：
DECLARE @buffer tinyint;
DECLARE @duration tinyint;
SET @buffer = 15;
SET @duration = 30;
WITH CTE
AS (
SELECT timeSlots.*, RowNumber = ROW_NUMBER() OVER( ORDER BY start_date_time ASC )
FROM (
-- Create an "appointment" to define the start of the block of availability.
-- If the start of the availability is still in the future, use the actual start_date_time
-- as both the start and end_date_time of the "appointment". If not, calculate the first
-- possible availability within this block based on the current time, a number of @buffer
-- minutes (to allow the user time to complete booking, etc., and increments based on the
-- duration of appointments (e.g., if current time + @buffer is 11:32, but appointments are
-- 15 minutes each on the hour, round up to 11:45).
-- Only look up to 2 weeks out.
SELECT
availability.id,
'Start' AS type,
availability.professional_id,
availability.start_date_time,
Iif(availability.start_date_time > DateAdd(Minute, @buffer, GetDate()), availability.start_date_time, DateAdd(Minute, (@duration - (DateDiff(Minute, availability.start_date_time, DateAdd(Minute, DateDiff(Minute, 0, DateAdd(Second, 30, DateAdd(Minute, @buffer, GetDate()))), 0)) % @duration)), DateAdd(Minute, DateDiff(Minute, 0, DateAdd(Second, 30, DateAdd(Minute, @buffer, GetDate()))), 0))) AS end_date_time
FROM
availability
WHERE
availability.end_date_time > GetDate()
AND availability.end_date_time <= DateAdd(Week, 2, GetDate())
UNION
-- Create an "appointment" to define the end of the block of availability.
-- Only look up to 2 weeks out.
SELECT
availability.id,
'End' AS type,
availability.professional_id,
availability.end_date_time as start_date_time,
availability.end_date_time AS end_date_time
FROM
availability
WHERE
availability.end_date_time > GetDate()
AND availability.end_date_time <= DateAdd(Week, 2, GetDate())
UNION
-- Get alreasy scheduled appointments up to 2 weeks out.
SELECT
appointment.id,
'Appointment' AS type,
appointment.professional_id,
appointment.start_date_time,
appointment.end_date_time
FROM
appointment
WHERE
start_date_time >= GetDate()
AND start_date_time <= DateAdd(Week, 2, GetDate())
) AS timeSlots
)
SELECT
TOP 5
a.professional_id,
min( a.end_date_time ) AS next_availability
FROM
CTE a
INNER JOIN CTE b
ON a.RowNumber = b.RowNumber - 1
AND a.professional_id = b.professional_id
WHERE
dateDiff( Minute, a.end_date_time, b.start_date_time) >= @duration
-- Restrict results to those where the start of the gap is at least @buffer away from current time
AND a.end_date_time >= DateAdd(Minute, @buffer, GetDate())
AND a.type <> 'End'
GROUP BY
a.professional_id
ORDER BY
next_availability ASC
编辑以解释工作查询的作用：
上面查询中的CTE生成了一个表，该表本质上包括每个预订约会1行。该计划是在这些任命之间找到足够持续时间的差距。为了为这些任命创建界限，还根据开始和终点的日期/时间来包括"开始"任命和"结束"任命。为了确保不包括已经过去了的时间差距，" start"的start_date_time是可用性的start_date_time，或者当前日期和时间（已调整到下一个slot shart_date_time，如果start_date_time调整为start_date_time）已经过去了。
作为示例，给定上面的示例数据，CTE将返回以下内容（显示专业名称而不是ID）：
id | type | name | start_date_time | end_date_time
--- | ----------- | ----- | ------------------- | -------------------
1 | Start | Bob | 03/06/2017 09:00:00 | 03/06/2017 09:00:00
1 | Appointment | Bob | 03/06/2017 09:00:00 | 03/06/2017 09:30:00
1 | Appointment | Bob | 03/06/2017 10:00:00 | 03/06/2017 10:30:00
1 | End | Bob | 03/06/2017 12:30:00 | 03/06/2017 12:30:00
2 | Start | Bob | 03/06/2017 13:30:00 | 03/06/2017 13:30:00
2 | End | Bob | 03/06/2017 18:00:00 | 03/06/2017 18:00:00
3 | Start | Frank | 03/06/2017 10:00:00 | 03/06/2017 10:00:00
3 | Appointment | Frank | 03/06/2017 10:00:00 | 03/06/2017 10:30:00
4 | Appointment | Frank | 03/06/2017 10:30:00 | 03/06/2017 11:00:00
5 | Appointment | Frank | 03/06/2017 11:00:00 | 03/06/2017 11:30:00
3 | End | Frank | 03/06/2017 14:00:00 | 03/06/2017 14:00:00
4 | Start | Joe | 03/07/2017 08:30:00 | 03/07/2017 08:30:00
4 | End | Joe | 03/07/2017 16:30:00 | 03/07/2017 16:30:00
鉴于上述CTE结果，您可以看到鲍勃在3/06的9:00开始工作，并在该日期预定了2个约会，一个是9：00-9：30，一个从10：00-10开始：30。外部查询所做的是取上表并将其加入，并将其加入一行，以使上面的数据看起来像（所有日期均为3/06）：
a.type | a.start | a.end | b.type | b.start | b.end
------ | -------- | -------- | ------ | -------- | --------
Start | 09:00:00 | 09:00:00 | Appt | 09:00:00 | 09:30:00
Appt | 09:00:00 | 09:30:00 | Appt | 10:00:00 | 10:30:00
Appt | 10:00:00 | 10:30:00 | End | 12:30:00 | 12:30:00
End | 12:30:00 | 12:30:00 | Start | 13:30:00 | 13:30:00
外部查询然后过滤这些结果仅返回A.End和B.Start之间的差异至少持续时间长的那些行。第一行不起作用，因为第一行A.End（9:00）和第一行B.Start（9:00）之间的差异小于持续时间。第二行确实工作是因为第二行A.End（9:30）和第二行B.Start（10:00）之间的差异足够持续时间。这样，外部查询仅返回那些足够持续时间的时间插槽，然后仅返回每个专业的第一个。
好吧，您去：
DECLARE @buffer tinyint;
DECLARE @duration tinyint;
SET @buffer = 15;
SET @duration = 30;
WITH TheDates AS
(
SELECT
availability.id,
availability.professional_id,
availability.start_date_time,
Iif(availability.start_date_time > DateAdd(Minute, @buffer, GetDate()), availability.start_date_time, DateAdd(Minute, (@duration - (DateDiff(Minute, availability.start_date_time, DateAdd(Minute, DateDiff(Minute, 0, DateAdd(Second, 30, DateAdd(Minute, @buffer, GetDate()))), 0)) % @duration)), DateAdd(Minute, DateDiff(Minute, 0, DateAdd(Second, 30, DateAdd(Minute, @buffer, GetDate()))), 0))) AS end_date_time,
availability.end_date_time AS end_date_time_real
FROM availability
WHERE
availability.end_date_time > GetDate()
AND availability.end_date_time <= DateAdd(Week, 2, GetDate())
), dates_filtered AS
(
SELECT
id,
professional_id,
start_date_time,
end_date_time,
end_date_time_real
FROM TheDates
WHERE dateDiff( Minute, end_date_time, start_date_time) >= @duration
AND end_date_time >= DateAdd(Minute, @buffer, GetDate())
), existingApts AS
(
-- Get alreasy scheduled appointments up to 2 weeks out.
SELECT
appointment.id,
appointment.professional_id,
appointment.start_date_time,
appointment.end_date_time
FROM appointment
WHERE
start_date_time >= GetDate()
AND start_date_time <= DateAdd(Week, 2, GetDate())
), dates_without_apt AS
( -- filter out items with apt between start and end or starting or ending.
SELECT
id,
professional_id,
-- start_date_time,
-- end_date_time,
end_date_time_real
FROM dates_filtered D
LEFT JOIN existingApts A ON
-- apt between
(D.start_date_time <= A.start_date_time AND D.end_date_time_real >= A.end_date_time) OR
-- apt ends in range
(A.end_date_time >= D.start_date_time AND A.end_date_time <= D.end_date_time_real) OR
-- apt starts in range
(A.start_date_time >= D.start_date_time AND A.start_date_time <= D.end_date_time_real) OR
WHERE A.id is null
)
SELECT
TOP 5
a.professional_id,
min( a.end_date_time_real ) AS next_availability
FROM dates_without_apt a
ORDER BY start_date_time ASC