SQL Server如何将15分钟的时间间隔重新分组为1分钟? [英] SQL Server how to regroup interval of 5 minute into 1 of 15 minute?
问题描述
我正在建立一个网站,您可以在线接受任命。我不会详细解释,但我有一张桌子和我的空闲时间可以预约。分布在5分钟的时间间隔内。以下是一个例子:
ID开始日期结束日期
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.0 00
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
根据咨询时间的不同,根据咨询原因,我必须将这些行组合成一个并知道min(IDSchedulingInterval)和max(IDSchedulingInterval)。
下面是我想要的结果示例,如果持续时间为15分钟:
最小(ID)最大(ID)开始日期结束日期
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
持续时间可以改变。我不知道如何继续查询。
编辑
这里有一些例外,你必须检查。这是我的表
ID开始日期结束日期保存
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 0 9: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 )最大(ID)开始日期结束日期
492560 492565 2016-12-16 09:00:00.000 2016-12-16 09:30:00.000
只有1行将被退回,因为您没有足够的时间间隔
编辑2
感谢DVT我修改了查询,并且我几乎让我的查询工作成为唯一的时间点。这里是我的查询:
DECLARE @newinterval INT = 60;
; 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(从RDV_tbSchedulingByInterval中选择SUM(5),其中IdSchedulingByInterval
位于cte.IdSchedulingByIntervalMin和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.0 00 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
49 2568 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 $ b预期结果:
<$> c $ c> 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
我不想重叠其他的
这是最简单的,我可以提出没有更多细节
; WITH Tally AS
(
SELECT ROW_NUMBER()OVER(ORDER BY(SELECT 1))AS N FROM master.sys.objects A,master.sys.objects B
)
,间隔AS
(
SELECT
N AS ID,
DATEADD(MINUTE,(N-1)* 5,'20160101')AS StartDate,
DATEADD(MINUTE,(N)* 5,'20160101')AS EndDate
FROM Tally
)
SELECT MIN(Id)AS MinId,MAX(Id)AS MaxId, MIN(StartDate),MAX(EndDate)FROM Int ervals
GROUP BY CAST(StartDate AS Date),DATEPART(HOUR,StartDate),DATEPART(MINUTE,StartDate)/ 15
ORDER BY MinId
$ b 编辑:
只需用
SELECT
MIN(IdSchedulingByInterval)AS MinId,
MAX(IdSchedulingByInterval)AS MaxId,
MIN( SchedulingByIntervalStartDate),
MAX(SchedulingByIntervalEndDate)
FROM RDV_tbSchedulingByInterval
GROUP BY CAST(SchedulingByIntervalStartDate AS Date),DATEPART(HOUR,SchedulingByIntervalStartDate),DATEPART(MINUTE,SchedulingByIntervalStartDate)/ 15
ORDER BY MinId
I'm building up a website where you can take an appointement online. I'll not explain all in detail but I have an table with my available time to take an appointment. Distributed into intervals of 5 min. Here's an example:
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
Depending the consultation time, based in the reason for consultation, I have to group these rows into one and know the min(IDSchedulingInterval) and the max(IDSchedulingInterval).
Here's an example of the result I want if I have a duration time of 15 min:
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
The duration time can change. I don't know how to proceed to make this query..
EDIT Here are some exception you have to check. Here's my table
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
Here the time between 8:45 to 9:00 is reserved so you can't take it. Also you don't have time between 8:25 and 8:35 so you can't reserved it either. An example, if I want to take a appointment of 30 min then I should have a result like this one:
Min(ID) Max(ID) StartDate EndDate
492560 492565 2016-12-16 09:00:00.000 2016-12-16 09:30:00.000
Only 1 row will be returned because you don't have enough time between other intervals
EDIT 2
Thanks to DVT I have modified is query and i'm almost having my query work the only hic here is the overlapping time. here's my query:
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
Here's my result:
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
Expected result:
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
I don't want time to overlapped other
This is the easiest i could come up with without more specifics
;WITH Tally AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N FROM master.sys.objects A, master.sys.objects B
)
,Intervals AS
(
SELECT
N AS Id,
DATEADD(MINUTE, (N-1)*5, '20160101') AS StartDate,
DATEADD(MINUTE, (N)*5, '20160101') AS EndDate
FROM Tally
)
SELECT MIN(Id) AS MinId, MAX(Id) AS MaxId, MIN(StartDate), MAX(EndDate) FROM Intervals
GROUP BY CAST(StartDate AS Date), DATEPART(HOUR,StartDate), DATEPART(MINUTE, StartDate) / 15
ORDER BY MinId
EDIT:
Just replace the names with your table like
SELECT
MIN(IdSchedulingByInterval) AS MinId,
MAX(IdSchedulingByInterval) AS MaxId,
MIN(SchedulingByIntervalStartDate), ¨
MAX(SchedulingByIntervalEndDate)
FROM RDV_tbSchedulingByInterval
GROUP BY CAST(SchedulingByIntervalStartDate AS Date), DATEPART(HOUR,SchedulingByIntervalStartDate), DATEPART(MINUTE, SchedulingByIntervalStartDate) / 15
ORDER BY MinId
这篇关于SQL Server如何将15分钟的时间间隔重新分组为1分钟?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!