I have a callcentre dataset, with CallStartDateTime and [duration] (in seconds).

For example '2021-12-10 16:14:47.000', '1140' So this should be a start time of '2021-12-10 16:14:47.000' and an end time of '2021-12-10 16:33:47.000'.
What I need to show is in 1/4 of an hour intervals.

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' 

Obviously this is a simplified version of what I am looking for, but looking for suggestions. I have joined it to a "Time" type table but am struggling with the maths on getting this to work. Any help would be appreciated.

Working on SQL database in Azure

CodePudding user response：

Took a more complex approach to avoid any slow loops. Not sure your requirements, but this should scale pretty well for larger volumes of data, which I'd assume you have if you are processing call center data. I did my best to comment each layer to make it as intuitive as possible

Also I just used a Tally Table generator from online, believe Azure SQL now has one built in, but haven't used it and don't have access to Azure SQL version to play around in.

Return Overlap in Seconds with Each 15 Minute Interval

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