如何在TSQL中为电话呼叫生成每季度分配的分钟数

Question

我有一个呼叫中心数据集，包含CallStartDateTime和[持续时间](以秒为单位(。

例如"2021-12-10 16:14:47.000"、"1140"因此，这应该是"2021-12-10 16:14:47.000"的开始时间结束时间为"2021-12-10 16:33:47.000"。
我需要以1/4小时的间隔显示。

So, [CallStartQuarterHour],[DurationInSeconds]

1.'2021-12-10 16:00:00.000' '13'    
2.'2021-12-10 16:15:00.000' '900'     
3.'2021-12-10 16:30:00.000' '227' 

很明显，这是我想要的一个简化版本，但在寻找建议。我加入了一个"；时间"；打字，但我在数学上很吃力。如有任何帮助，我们将不胜感激。

在Azure 中处理SQL数据库

Answer 1

采用了更复杂的方法来避免任何缓慢的循环。不确定您的需求，但这应该可以很好地扩展到更大的数据量，如果您正在处理呼叫中心数据，我认为您已经具备了这种能力。我尽我所能评论每一层，使其尽可能直观

此外，我刚刚使用了在线的Tally Table生成器，相信Azure SQL现在已经内置了一个，但还没有使用过，也没有访问Azure SQL版本。

每隔15分钟返回重叠(秒(

DROP TABLE IF EXISTS #CallDuration 
CREATE TABLE #CallDuration (
ID INT IDENTITY(1,1)
,CallStartTime DATETIME
,Duration INT
)
INSERT INTO #CallDuration
VALUES ('2021-12-10 16:14:47.000',1140)
,('2021-12-31 16:30:30.000',541) /*Added this to simulate more data*/
,('2022-08-31 01:31:30.000',10) /*Added scenario where ends in 1 interval*/

SELECT 
A.ID
,A.CallStartTime
,B.CallEndTime
,A.Duration
,B.NumOfIntervals
,D.IntervalStartDatetime
,D.IntervalEndDatetime
,OverlappingSeconds = DateDiff(Second,E.OverlappingRangeStartDateTime,E.OverlappingRangeEndDateTime)
FROM #CallDuration AS A
CROSS APPLY (
SELECT FirstIntervalDateTime = DATEADD(MINUTE, ( DATEDIFF(MINUTE,0,A.CallStartTime)/15) * 15, 0) /*Round down to last 15 minute increment*/
,CallEndTime = DATEADD(Second,A.Duration,A.CallStartTime) /*Find when the call ended*/
,NumOfIntervals = CEILING(DATEDIFF(MINUTE,0,DATEADD(Second,A.Duration,A.CallStartTime))/15.0) - CEILING(DATEDIFF(MINUTE,0,A.CallStartTime)/15.0) + 1 /*Calculates how many intervals will overlap*/
) AS B
CROSS APPLY dbo.GetNumsAB(0,B.NumOfIntervals-1,1,1) AS C    /*Tally table generator (could use a manual tally table as well). Definiton here: https://www.sqlservercentral.com/scripts/getnumsAB
This function generates 1 row per value for input range
FYI Tally table a common term. Basically table of incrementing values to avoid looping in SQL
*/
CROSS APPLY (
/*Defines each 15 minute interval*/
SELECT IntervalStartDatetime = DATEADD(MINUTE,15 * n1,FirstIntervalDateTime)
,IntervalEndDatetime = DATEADD(MINUTE,15 * n2,FirstIntervalDateTime)                    
) AS D
CROSS APPLY (
/*Find overlap between 15 minute interval start/end times and Call start/end times. 
Does this by putting all 4 dates into a single column, ordering them, then grabbing second and third values*/
SELECT 
OverlappingRangeStartDateTime = RangeDatetime /*Grabs second row*/
,OverlappingRangeEndDateTime = LEAD(RangeDatetime,1) OVER (ORDER BY DTA.RangeDateTime) /*Order of operations magic to grab 3rd row. Lead runs before FETCH, so can grab third row*/
FROM (
VALUES (A.CallStartTime),(D.IntervalStartDatetime),(D.IntervalEndDatetime),(B.CallEndTime)
) AS DTA(RangeDatetime)
ORDER BY DTA.RangeDatetime
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
) AS E