使用SQL计算间隔时间

Question

我需要计算30分钟间隔内的呼叫等待时间(保持时间)

HoldTime以秒为单位

输入数据

| Date     | ID    |StartDate | StartTime          |holdtime |
---------  ------- ---------- -------------------- ---------
28/12/2022  3110522 28/12/2022  10:03:46.0000000    62
28/12/2022  3110522 28/12/2022  10:36:42.0000000    189
28/12/2022  3110522 28/12/2022  11:06:54.0000000    65
28/12/2022  3110522 28/12/2022  11:11:46.0000000    79
28/12/2022  3110522 28/12/2022  11:19:55.0000000    118
28/12/2022  3110522 28/12/2022  11:38:20.0000000    36
28/12/2022  3110522 28/12/2022  12:13:46.0000000    67
28/12/2022  3110522 28/12/2022  13:45:27.0000000    24
28/12/2022  3110522 28/12/2022  13:52:59.0000000    144
28/12/2022  3110522 28/12/2022  15:02:43.0000000    39
28/12/2022  3110522 28/12/2022  16:00:41.0000000    246
28/12/2022  3110522 28/12/2022  16:54:22.0000000    79
28/12/2022  3110522 28/12/2022  16:59:18.0000000    94
28/12/2022  3110522 28/12/2022  17:29:19.0000000    84
28/12/2022  3110522 28/12/2022  17:54:44.0000000    64

输出数据

|ID    | StartDate |intervalStartTime | intervalStoptTime | holdtime
------  ---------  -----------------  ------------------- ----------
3110522 28/12/2022  10:00:00.000       10:30:00.0000000     62
3110522 28/12/2022  10:30:00.000       11:00:00.0000000     189
3110522 28/12/2022  11:00:00.000       11:30:00.0000000     262
3110522 28/12/2022  11:30:00.000       12:00:00.0000000     36
3110522 28/12/2022  12:00:00.000       12:30:00.0000000     67
3110522 28/12/2022  13:30:00.000       14:00:00.0000000     168
3110522 28/12/2022  15:00:00.000       15:30:00.0000000     39
3110522 28/12/2022  16:00:00.000       16:30:00.0000000     246
3110522 28/12/2022  16:30:00.000       17:00:00.0000000     121
3110522 28/12/2022  17:00:00.000       17:30:00.0000000     93
3110522 28/12/2022  17:30:00.000       18:00:00.0000000     107

我正在做一个函数来分割数据，但是我没有得到我想要的。我想我做的太复杂了，解不出来。我不喜欢在SQL中使用像WHILE这样的循环。

while (@eventDurationMins>0)
begin
set @eventDurationInIntervalMins = cast(@intervalEndTime-@eventStartTime as float)*24*60 ;
if @eventDurationMins<@eventDurationInIntervalMins
set @eventDurationInIntervalMins = @eventDurationMins ;
insert into @retTable
select @intervalStartTime,@intervalEndTime,@eventDurationInIntervalMins
set @eventDurationMins = @eventDurationMins - @eventDurationInIntervalMins ;
set @eventStartTime = @intervalEndTime;
set @intervalStartTime = @intervalEndTime;
set @intervalEndTime = dateadd(minute,@intervalMins,@intervalEndTime);
end;

提前感谢，

Answer 1

这里的关键是找到30分钟内的时间段。TimeSlot计算基本上是"四舍五入";时间要每隔30分钟。示例:从10:00到10:29四舍五入到10:00等

select ID, StartDate, 
intervalStartTime  = TimeSlot, 
intervalStoptTime  = dateadd(minute, 30, TimeSlot),
holdtime  = sum(holdtime)
from
(
select *, 
TimeSlot = convert(time(0), 
dateadd(second, 
convert(int, 
datediff(second, '00:00:00', StartTime) 
/ 30.0 / 60.0) * 30 * 60, '00:00'))
from   InputData
) i
group by  ID, StartDate, TimeSlot

编辑:

---

CTE数字只是一个计数表。它用于扩展那些holdTime跨越多个时隙的行

保持时间(ht)在下面的case表达式中计算。第一个条件是保持时间包含在同一时隙内。其余3种情况是处理爆炸时的情况

ht = case when n = 0 
and  count(*) over(partition by [Date], [ID], [StartDate], [StartTime]) = 1
then holdTime
when n = 0
then datediff(second, StartTime, dateadd(minute, 30, TimeSlot))
when n = count(*) over(partition by [Date], [ID], [StartDate], [StartTime]) - 1
then datediff(second, dateadd(minute, n * 30, TimeSlot), EndTime)
else 30 * 60
end

查询

with 
numbers as
(
select n = 0
union all
select n = n + 1
from   numbers
where  n < 99
),
cte as
(
select *
from   InputData i
cross apply
(
select EndTime  = dateadd(second, holdTime, StartTime),
TimeSlot = convert(time(0), 
dateadd(second, 
convert(int, 
datediff(second, '00:00:00', StartTime) 
/ 30.0 / 60.0) * 30 * 60, '00:00'))  
) t
cross apply
(
select EndTimeSlot = convert(time(0), 
dateadd(second, 
convert(int, 
datediff(second, '00:00:00', EndTime) 
/ 30.0 / 60.0) * 30 * 60, '00:00'))  
) e
),
cte2 as
(
select [Date], [ID], [StartDate], [StartTime], [EndTime],
TimeSlot = dateadd(minute, n * 30, TimeSlot),
ht = case when n = 0 
and  count(*) over(partition by [Date], [ID], [StartDate], [StartTime]) = 1
then holdTime
when n = 0
then datediff(second, StartTime, dateadd(minute, 30, TimeSlot))
when n = count(*) over(partition by [Date], [ID], [StartDate], [StartTime]) - 1
then datediff(second, dateadd(minute, n * 30, TimeSlot), EndTime)
else 30 * 60
end
from   cte i
inner join numbers n 
on n.n >= 0 
and n.n <= datediff(minute, TimeSlot, EndTimeSlot) / 30
)
select ID, StartDate, 
intervalStartTime  = TimeSlot, 
intervalStoptTime  = dateadd(minute, 30, TimeSlot),
holdtime  = sum(ht)
from   cte2
group by ID, StartDate, TimeSlot
order by intervalStartTime

,db&lt的在小提琴演示