Sum Duration Across Appropriate Intervals

I need some help. I have a table of individual contacts (i.e. phone calls) that contains the arrival time and the duration. I need to be able to count the number of contacts received in each 15 minute interval, as well as sum the "Talk Time" across each interval. The answered count will peg in the interval in which it arrived, and the appropriate portion of the duration will count at each interval in which the "clock was running" for the contact.

For example, a contact arrives at 2018-12-06 07:15:01.000, lasts for over 15 minutes (947 seconds). How would I get 900 seconds to appear in the 7:15 interval, but the remaining 47 seconds to appear in the 7:30 interval?

Below is a bit of test data in the form of a CTE.

I've got the 15 minute interval part figured out, but I have no idea how to tackle the duration sum. I've tried a few things but my brain gets stuck. I might be able to figure it out eventually, but the direction I take on this project hinges on whether I can get this beat quickly. I know it is possible because the built in report viewer on the source GUI can do it.

I would sure appreciate any help I can get.

;with cteTestData as (
SELECT 1 as ID, '2018-12-06 07:03:27.000' as ContactStartTime, Sum(Left('00:04:28',2) * 3600 + substring('00:04:28', 4,2) * 60 + substring('00:04:28', 7,2)) as Duration
UNION ALL
SELECT 2 as ID, '2018-12-06 07:03:32.000' as ContactStartTime, Sum(Left('00:14:28',2) * 3600 + substring('00:14:28', 4,2) * 60 + substring('00:14:28', 7,2)) as Duration
UNION ALL
SELECT 3 as ID, '2018-12-06 07:08:12.000' as ContactStartTime, Sum(Left('00:10:03',2) * 3600 + substring('00:10:03', 4,2) * 60 + substring('00:10:03', 7,2)) as Duration
UNION ALL
SELECT 4 as ID, '2018-12-06 07:14:59.000' as ContactStartTime, Sum(Left('00:02:58',2) * 3600 + substring('00:02:58', 4,2) * 60 + substring('00:02:58', 7,2)) as Duration
UNION ALL
SELECT 5 as ID, '2018-12-06 07:15:01.000' as ContactStartTime, Sum(Left('00:15:47',2) * 3600 + substring('00:15:47', 4,2) * 60 + substring('00:15:47', 7,2)) as Duration
UNION ALL
SELECT 6 as ID, '2018-12-06 07:15:12.000' as ContactStartTime, Sum(Left('00:08:18',2) * 3600 + substring('00:08:18', 4,2) * 60 + substring('00:08:18', 7,2)) as Duration
UNION ALL
SELECT 7 as ID, '2018-12-06 07:18:50.000' as ContactStartTime, Sum(Left('00:10:22',2) * 3600 + substring('00:10:22', 4,2) * 60 + substring('00:10:22', 7,2)) as Duration
UNION ALL
SELECT 8 as ID, '2018-12-06 07:20:05.000' as ContactStartTime, Sum(Left('00:03:11',2) * 3600 + substring('00:03:11', 4,2) * 60 + substring('00:03:11', 7,2)) as Duration
UNION ALL
SELECT 9 as ID, '2018-12-06 07:29:32.000' as ContactStartTime, Sum(Left('00:32:53',2) * 3600 + substring('00:32:53', 4,2) * 60 + substring('00:32:53', 7,2)) as Duration
UNION ALL
SELECT 10 as ID, '2018-12-06 07:35:17.000' as ContactStartTime, Sum(Left('00:07:37',2) * 3600 + substring('00:07:37', 4,2) * 60 + substring('00:07:37', 7,2)) as Duration
)
select ID, ContactStartTime
, DATEADD(minute, (DATEDIFF( minute, 0, ContactStartTime) / 15) * 15, 0) AS ContactStartInterval
, Duration

FROM cteTestData
ORDER BY ContactStartTime

hi

I have the final product

earlier I had given some clues in posts
I have deleted those posts

i have used tally table ..

please let me know

if i am missing something

or

if i don't understand what is EXPECTED results

njoy
:slight_smile:
:slight_smile:

SQL .. using tally table ..
USE tempdb 

go 

; 
WITH ctetestdata 
     AS (SELECT 1                                AS ID, 
                '2018-12-06 07:03:27.000'        AS ContactStartTime, 
                Sum(LEFT('00:04:28', 2) * 3600 + Substring('00:04:28', 4, 2) * 
                                                 60 + 
                    Substring('00:04:28', 7, 2)) AS Duration 
         UNION ALL 
         SELECT 2                                AS ID, 
                '2018-12-06 07:03:32.000'        AS ContactStartTime, 
                Sum(LEFT('00:14:28', 2) * 3600 + Substring('00:14:28', 4, 2) * 
                                                 60 + 
                    Substring('00:14:28', 7, 2)) AS Duration 
         UNION ALL 
         SELECT 3                                AS ID, 
                '2018-12-06 07:08:12.000'        AS ContactStartTime, 
                Sum(LEFT('00:10:03', 2) * 3600 + Substring('00:10:03', 4, 2) * 
                                                 60 + 
                    Substring('00:10:03', 7, 2)) AS Duration 
         UNION ALL 
         SELECT 4                                AS ID, 
                '2018-12-06 07:14:59.000'        AS ContactStartTime, 
                Sum(LEFT('00:02:58', 2) * 3600 + Substring('00:02:58', 4, 2) * 
                                                 60 + 
                    Substring('00:02:58', 7, 2)) AS Duration 
         UNION ALL 
         SELECT 5                                AS ID, 
                '2018-12-06 07:15:01.000'        AS ContactStartTime, 
                Sum(LEFT('00:15:47', 2) * 3600 + Substring('00:15:47', 4, 2) * 
                                                 60 + 
                    Substring('00:15:47', 7, 2)) AS Duration 
         UNION ALL 
         SELECT 6                                AS ID, 
                '2018-12-06 07:15:12.000'        AS ContactStartTime, 
                Sum(LEFT('00:08:18', 2) * 3600 + Substring('00:08:18', 4, 2) * 
                                                 60 + 
                    Substring('00:08:18', 7, 2)) AS Duration 
         UNION ALL 
         SELECT 7                                AS ID, 
                '2018-12-06 07:18:50.000'        AS ContactStartTime, 
                Sum(LEFT('00:10:22', 2) * 3600 + Substring('00:10:22', 4, 2) * 
                                                 60 + 
                    Substring('00:10:22', 7, 2)) AS Duration 
         UNION ALL 
         SELECT 8                                AS ID, 
                '2018-12-06 07:20:05.000'        AS ContactStartTime, 
                Sum(LEFT('00:03:11', 2) * 3600 + Substring('00:03:11', 4, 2) * 
                                                 60 + 
                    Substring('00:03:11', 7, 2)) AS Duration 
         UNION ALL 
         SELECT 9                                AS ID, 
                '2018-12-06 07:29:32.000'        AS ContactStartTime, 
                Sum(LEFT('00:32:53', 2) * 3600 + Substring('00:32:53', 4, 2) * 
                                                 60 + 
                    Substring('00:32:53', 7, 2)) AS Duration 
         UNION ALL 
         SELECT 10                               AS ID, 
                '2018-12-06 07:35:17.000'        AS ContactStartTime, 
                Sum(LEFT('00:07:37', 2) * 3600 + Substring('00:07:37', 4, 2) * 
                                                 60 + 
                    Substring('00:07:37', 7, 2)) AS Duration), 
     cte 
     AS (SELECT id, 
                contactstarttime, 
                Dateadd(minute, ( Datediff(minute, 0, contactstarttime) / 15 ) * 
                                15, 
                0) 
                   AS 
                ContactStartInterval, 
                duration 
         FROM   ctetestdata), 
     cte2 
     AS (SELECT id, 
                contactstarttime, 
                contactstartinterval, 
                duration, 
                duration / 900 AS HowMany_Intervals_ToAdd, 
                duration % 900 AS LastInterval_Duration_Left 
         FROM   cte), 
     tally_cte 
     AS (SELECT N= 0 
         UNION ALL 
         SELECT n + 1 
         FROM   tally_cte 
         WHERE  n + 1 < 90) 
SELECT a.*, 
       b.id, 
       b.contactstarttime, 
       Dateadd(minute, a.n * 15, b.contactstartinterval) as Intervals, 
       CASE 
         WHEN a.n < b.howmany_intervals_toadd THEN 900 
         ELSE lastinterval_duration_left 
       END as Duration 
FROM   tally_cte a 
       JOIN cte2 b 
         ON a.n <= b.howmany_intervals_toadd 
ORDER  BY b.id 

go
All Results

Original Data for id = 5

image

result where id = 5 .. showing

Original data for id = 9

image

result where id = 9 showing

I don't think this is quite right. If you look at contact #2, it should cross into two intervals, the 7:00 and the 7:15. 688 and 180 seconds, respectively. Your query results show all 868 seconds in the 7:00 interval.

hi paul

I got the issue

i know what i did .. but did not understand what you were looking for
i was taking start time as 7:00 from start interval
instead i should have been taking 7:05 from starttime

> am i correct ?

i can modify my SQL very easily to do this
please let me know
thanks
:slight_smile:
:slight_smile:

I am not sure I follow. Where would 7:05 come in? I would need the time between 2018-12-06 07:03:32.000 and the new 7:15 interval, and then the time between the 7:15 interval and the contact end time of 2018-12-06 07:18:00.000.

I think i have a solution now (with help from another user on a different forum), but if you want to keep working on this just out of curiosity, we can.

i was just giving an example ....

what i meant was
i calculated the time interval stating point as 7:00
and substracted 868 seconds
`

starting from 7:00

`

i should have instead substracted 868 seconds

> starting from at 7:03