# 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 ..

if i am missing something

or

if i don't understand what is EXPECTED results

njoy

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 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
ORDER  BY b.id

go
``````
All Results

Original Data for id = 5

result where id = 5 .. showing

Original data for id = 9

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
thanks

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`