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