Calculate total "talk" time from the HDS?

[david.macias]

I'm working on trying to get total minutes of usage in and out from UCCE. Said differently, I want to know the total time a call is active on the system. IVR, Agent, etc. Customer would like to get an idea from the CC how many minutes they are consuming. While I know this is better suited as a telco report I only have one hammer right now the TCD/RCD. Has anyone done something like this before? I'm thinking to just add up duration for every call leg to start then refine it from there.

david

[david.macias]

Here's what I've come up with so far.

First, you can't get inbound and outbound at the same time. So let's start with outbound which should be easier. We know that any manual call an agent makes from the agent line will be attributed to the default skill group in CCE. So this should capture it:

SELECT SUM(Duration) AS Seconds, SUM(Duration)/60 AS Minutes, SUM(Duration)/3600 AS Hours FROM t_Termination_Call_Detail 
WHERE SkillGroupSkillTargetID = 5000 AND CallDisposition IN (7, 10, 13, 14, 6, 29) AND (DigitsDialed LIKE '+%' OR DigitsDialed LIKE '9%' OR DigitsDialed LIKE '1%') AND DateTime > '08/10/2021' AND DateTime < '08/11/2021'

What I'm doing here is only looking at calls that go out to the PSTN with the disposition codes which would have generated "talk" time.

Now, for inbound it gets a bit tricky as there are a lot of different scenarios you have to account for. I tried to get this with the TCD, but it proved difficult so I decided to work on the RCD only and came up with this.

SELECT SUM(DATEDIFF(second, BeganRoutingDateTime, DateTime) + RouterQueueTime) AS TotalCallTimeSeconds FROM t_Route_Call_Detail
WHERE DateTime > '07/01/2021' AND DateTime < '08/01/2021' AND (ANI LIKE '+%' OR ANI LIKE '9%' OR ANI LIKE '1%')

This query will unfortunate inflate total time when a call abandons, but based on some testing it feels the closes to what I am trying to accomplish. I would love to hear what other think and how to make this better.

david

[guptaroy25]

I dont know much about hds but in IVR we start and end datetime and store it with (RoutingcallKey+RoutingCallKeyDay)ICM id later this is used by UCCE and they add this time with agent talk time and other times like queue , CCB etc

[david.macias]

Thanks for adding your two cents. Unfortunately, I can't modify the IVR so this all needs to come from the HDS DB.

david

[bill.king1]

You probably already thought about this, but what about getting this data from their call recording solution? Lately it seems like some systems like Calabrio allow you to attack a problem from that direction as opposed to the UCCE/CUIC angle for reports.

[david.macias]

That's a good idea, but that misses all IVR time also we're not recording 100% of the calls.

david