我正在建立一个网站,您可以在其中在线预约。我不会详细解释所有内容,但我有一张桌子,上面有我可以预约的时间。以 5 分钟为间隔进行分配。这是一个例子:
ID StartDate EndDate
492548 2016-12-16 08:00:00.000 2016-12-16 08:05:00.000
492549 2016-12-16 08:05:00.000 2016-12-16 08:10:00.000
492550 2016-12-16 08:10:00.000 2016-12-16 08:15:00.000
492551 2016-12-16 08:15:00.000 2016-12-16 08:20:00.000
492552 2016-12-16 08:20:00.000 2016-12-16 08:25:00.000
492553 2016-12-16 08:25:00.000 2016-12-16 08:30:00.000
492554 2016-12-16 08:30:00.000 2016-12-16 08:35:00.000
492555 2016-12-16 08:35:00.000 2016-12-16 08:40:00.000
492556 2016-12-16 08:40:00.000 2016-12-16 08:45:00.000
492557 2016-12-16 08:45:00.000 2016-12-16 08:50:00.000
492558 2016-12-16 08:50:00.000 2016-12-16 08:55:00.000
492559 2016-12-16 08:55:00.000 2016-12-16 09:00:00.000
492560 2016-12-16 09:00:00.000 2016-12-16 09:05:00.000
492561 2016-12-16 09:05:00.000 2016-12-16 09:10:00.000
492562 2016-12-16 09:10:00.000 2016-12-16 09:15:00.000
492563 2016-12-16 09:15:00.000 2016-12-16 09:20:00.000
492564 2016-12-16 09:20:00.000 2016-12-16 09:25:00.000
492565 2016-12-16 09:25:00.000 2016-12-16 09:30:00.000
492566 2016-12-16 09:30:00.000 2016-12-16 09:35:00.000
根据咨询时间,根据咨询原因,我必须将这些行分组为一并知道最小值(IDSchedulingInterval)和最大值(IDSchedulingInterval)。
如果持续时间为 15 分钟,我想要的结果示例如下:
Min(ID) Max(ID) StartDate EndDate
492548 492550 2016-12-16 08:00:00.000 2016-12-16 08:15:00.000
492551 492553 2016-12-16 08:15:00.000 2016-12-16 08:30:00.000
492554 492556 2016-12-16 08:30:00.000 2016-12-16 08:45:00.000
492557 492559 2016-12-16 08:45:00.000 2016-12-16 09:00:00.000
持续时间可以改变。我不知道如何继续进行此查询..
EDIT以下是您必须检查的一些例外情况。这是我的桌子
ID StartDate EndDate Isreserved
492548 2016-12-16 08:00:00.000 2016-12-16 08:05:00.000 0
492549 2016-12-16 08:05:00.000 2016-12-16 08:10:00.000 0
492550 2016-12-16 08:10:00.000 2016-12-16 08:15:00.000 0
492551 2016-12-16 08:15:00.000 2016-12-16 08:20:00.000 0
492552 2016-12-16 08:20:00.000 2016-12-16 08:25:00.000 0
492555 2016-12-16 08:35:00.000 2016-12-16 08:40:00.000 0
492556 2016-12-16 08:40:00.000 2016-12-16 08:45:00.000 0
492557 2016-12-16 08:45:00.000 2016-12-16 08:50:00.000 1
492558 2016-12-16 08:50:00.000 2016-12-16 08:55:00.000 1
492559 2016-12-16 08:55:00.000 2016-12-16 09:00:00.000 1
492560 2016-12-16 09:00:00.000 2016-12-16 09:05:00.000 0
492561 2016-12-16 09:05:00.000 2016-12-16 09:10:00.000 0
492562 2016-12-16 09:10:00.000 2016-12-16 09:15:00.000 0
492563 2016-12-16 09:15:00.000 2016-12-16 09:20:00.000 0
492564 2016-12-16 09:20:00.000 2016-12-16 09:25:00.000 0
492565 2016-12-16 09:25:00.000 2016-12-16 09:30:00.000 0
492566 2016-12-16 09:30:00.000 2016-12-16 09:35:00.000 0
这里8:45到9:00之间的时间是预留的,所以不能坐。
另外,8:25 至 8:35 之间没有时间,因此也无法预订。举个例子,如果我想预约 30 分钟,那么我应该得到如下结果:
Min(ID) Max(ID) StartDate EndDate
492560 492565 2016-12-16 09:00:00.000 2016-12-16 09:30:00.000
仅返回 1 行,因为其他间隔之间没有足够的时间
EDIT 2
感谢 DVT,我修改了查询,我的查询几乎可以工作,这里唯一的问题是重叠时间。这是我的查询:
DECLARE @newinterval INT = 60;
;with cte as (
SELECT
t1.IdSchedulingByInterval AS IdSchedulingByIntervalMin
, t2.IdSchedulingByInterval AS IdSchedulingByIntervalMax
, t1.SchedulingByIntervalStartDate
, t2.SchedulingByIntervalEndDate
FROM
RDV_tbSchedulingByInterval t1
JOIN RDV_tbSchedulingByInterval t2 ON t2.SchedulingByIntervalStartDate = DATEADD(minute, @newinterval - 5, t1.SchedulingByIntervalStartDate)
) select * from cte where (select SUM(5) from RDV_tbSchedulingByInterval where IdSchedulingByInterval
between cte.IdSchedulingByIntervalMin and cte.IdSchedulingByIntervalMax ) = @newinterval
order by cte.SchedulingByIntervalStartDate
这是我的结果:
492551 492562 2016-12-16 08:15:00.000 2016-12-16 09:15:00.000
492552 492563 2016-12-16 08:20:00.000 2016-12-16 09:20:00.000
492553 492564 2016-12-16 08:25:00.000 2016-12-16 09:25:00.000
492554 492565 2016-12-16 08:30:00.000 2016-12-16 09:30:00.000
492555 492566 2016-12-16 08:35:00.000 2016-12-16 09:35:00.000
492556 492567 2016-12-16 08:40:00.000 2016-12-16 09:40:00.000
492557 492568 2016-12-16 08:45:00.000 2016-12-16 09:45:00.000
492558 492569 2016-12-16 08:50:00.000 2016-12-16 09:50:00.000
492559 492570 2016-12-16 08:55:00.000 2016-12-16 09:55:00.000
492560 492571 2016-12-16 09:00:00.000 2016-12-16 10:00:00.000
492561 492572 2016-12-16 09:05:00.000 2016-12-16 10:05:00.000
492562 492573 2016-12-16 09:10:00.000 2016-12-16 10:10:00.000
492563 492574 2016-12-16 09:15:00.000 2016-12-16 10:15:00.000
492564 492575 2016-12-16 09:20:00.000 2016-12-16 10:20:00.000
492565 492576 2016-12-16 09:25:00.000 2016-12-16 10:25:00.000
492566 492577 2016-12-16 09:30:00.000 2016-12-16 10:30:00.000
492567 492578 2016-12-16 09:35:00.000 2016-12-16 10:35:00.000
492568 492579 2016-12-16 09:40:00.000 2016-12-16 10:40:00.000
492569 492580 2016-12-16 09:45:00.000 2016-12-16 10:45:00.000
预期结果:
492551 492562 2016-12-16 08:15:00.000 2016-12-16 09:15:00.000
492563 492574 2016-12-16 09:15:00.000 2016-12-16 10:15:00.000
我不想让时间与其他时间重叠