SQL Query to pull Total Calls based on every call type

[harresh123]

Does anyone have a combined SQL query that pulls call count for every call type for the past one year in a DN--Call Type--Script--Total Calls relationship. I have the DN--Call Type--Script mapping just trying to expand on it to include Total Calls for past 1/2 years.

[TalkingScientist]

This is a little more complicated than you are asking. 

Calls usually don't route as simple as DN-CallType-Script
Usually it's DN-CallType-Script-CallType2-CallType3-CallType4-Queue
So which calltype are you wanting to total?

The DN CallType to script map would be pretty easy. I'll attach that below. 

I call calltypes 2 and 3 here "Counter CallTypes" because calls pass through there and you can see them in the Call_Type_Interval table but they aren't handled there. Then you have CallType4, the one that comes right before the queue. I call this one the Queue Call Type and that is where most of the stats people like to see reside. To report on this one you would need to query the Route_Call_Detail table and then it gets a little more complex but I'll post a sample for that as well. 

DN CallType to Script (using Call_Type_Map table)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2022-01-01 00:00:00.000'
SET @EndDate = '2023-01-01 00:00:00.000'

SELECT ct.calltypeid,
       ct.enterprisename          AS CallType,
       ms.enterprisename          AS Script,
       Sum(cti.callsoffered)      AS CallsOffered,
       Sum(cti.callshandled)      AS CallsHandled,
       Sum(cti.routercallsabandq) AS RouterCallsAbandQ
FROM   call_type_interval cti
       LEFT JOIN call_type ct
              ON cti.calltypeid = ct.calltypeid
       LEFT JOIN call_type_map ctm
              ON ct.calltypeid = ctm.calltypeid
       LEFT JOIN master_script ms
              ON ctm.masterscriptid = ms.masterscriptid
WHERE  cti.datetime >= @StartDate
       AND cti.datetime < @EndDate

       AND ms.EnterpriseName is not null

GROUP  BY ct.calltypeid,
          ct.enterprisename,
          ms.enterprisename
ORDER  BY calltype 

DN CallType Script based on Route_Call_Detail

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2022-02-08 00:00:00.000'
SET @EndDate = '2022-02-09 00:00:00.000'

/*
    This query should be run on the AW_DB.
  Make sure to update 'ucce_hds' reference in the FROM section with the actual instance name
*/
SELECT rcd.dialednumberid,
       dn.enterprisename AS DialedNumber,
       rcd.calltypeid,
       ct.enterprisename AS CallType,
       rcd.scriptid,
       ms.enterprisename AS ScriptName,
       Count(*)          AS Total
FROM   route_call_detail (nolock) rcd
       LEFT JOIN call_type (nolock) ct
              ON rcd.calltypeid = ct.calltypeid
       LEFT JOIN dialed_number (nolock) dn
              ON rcd.dialednumberid = dn.dialednumberid
       LEFT JOIN script (nolock)
              ON rcd.scriptid = script.scriptid
       LEFT JOIN master_script (nolock) ms
              ON script.masterscriptid = ms.masterscriptid
WHERE  rcd.datetime >= @StartDate
       AND rcd.datetime < @EndDate
GROUP  BY rcd.dialednumberid,
          dn.enterprisename,
          rcd.calltypeid,
          ct.enterprisename,
          rcd.scriptid,
          ms.enterprisename 

[harresh123]

Thanks. I was looking for the main call type that has dialed Number mapped just to audit which ones have not even gotten any calls for past 1 year. Based on data then look further into queue Call Type's from route_call_detail table.

[harresh123]

Is there a possibility to append DialedNumber string to DN call type to script query? 

[TalkingScientist]

Sorta,

This would assume that there is a 1 to 1 relationship between DialedNumberStrings and CallTypes.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2022-01-01 00:00:00.000'
SET @EndDate = '2023-01-01 00:00:00.000'

SELECT
dn.DialedNumberString
,ct.CallTypeID, ct.EnterpriseName as CallType
,ms.EnterpriseName as Script
,sum(cti.CallsOffered) as CallsOffered
,sum(cti.CallsHandled) as CallsHandled
,sum(cti.RouterCallsAbandQ) as RouterCallsAbandQ

FROM
Dialed_Number dn
LEFT JOIN Dialed_Number_Map dnm ON dn.DialedNumberID = dnm.DialedNumberID
LEFT JOIN Call_Type ct ON dnm.CallTypeID = ct.CallTypeID
LEFT JOIN Call_Type_Interval cti ON ct.CallTypeID = cti.CallTypeID
LEFT JOIN Call_Type_Map ctm ON ct.CallTypeID = ctm.CallTypeID
LEFT JOIN Master_Script ms ON ctm.MasterScriptID = ms.MasterScriptID
WHERE
cti.DateTime >=@StartDate and cti.DateTime <@EndDate
AND ms.EnterpriseName is not null

GROUP BY
dn.DialedNumberString
,ct.CallTypeID
,ct.EnterpriseName
,ms.EnterpriseName
ORDER BY CallType

[harresh123]

This is Perfect. Is there a way in SQL to look at the call type and if it is same then group the dialed numbers together. I.e. something like below. I have some Call types that got few hundred numbers and the CallOffered is same irrespective of dialed number that's probably because it is looking at call type which is fine. 

Original Result:

DialedNumberString CallTypeID CallType Script
1235545 5644 TestSvcs_Main TestSvcs_Test_EM
1235702 5644 TestSvcs_Main TestSvcs_Main_EM
1235703 5644 TestSvcs_Main TestSvcs_Main_EM

Desired Result:
1235545, 1235702, 1235703 5644 TestSvcs_Main TestSvcs_Test_EM

[TalkingScientist]

Yep, just put this on the end of the query:

ORDER BY ct.EnterpriseName, dn.DialedNumberString

[harresh123]

Perfect. Is there a way to check the queue to skill group nodes across all the scripts that has target requery disabled and then go by this list to enable the requery for the missing ones?

[TalkingScientist]

Just noticed this follow up question. Yes that is possible but then you get into the "I'm going to invoice you territory"

Handling the script data to get that kind of detail is complicated. You are better off just using the find nodes feature and look through each one manually.

Unless you got a budget...then hit me up

update: this read like I was trying to drum up business vs joking. If you want to do this yourself I recommend reading up on Gary Fern's findings. It will come down to using the script data table. You decode it then search for the node data you are looking for. 

https://ciscocontactcentersecrets.wordpress.com/

[harresh123]

This is ignoring the values with null in  CallsOffered. Only giving the Dialed Number to Call Type mapping where there is a value in Calls Offered. How could I get the ones with 0 calls offered as well. Essentially I would like to know what call types and scripts have no calls offered in the past 1 year.

[bill.king1]

I'm sure there are more elegant ways to do it, but you could simply look at all calls during your time period and the MasterScriptID that they hit, and see which ones don't have any volume. Keep in mind, though, that depending on how you script your calls, a call might go through a script but not ultimately terminate there, which wouldn't show in this type of query, so this wouldn't be a fullproof way to do it.

[TalkingScientist]

Went ahead and included it in the RCD version as well. This does not assume 1 to 1 but it also doesn't involve the mapping. 

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2022-02-08 00:00:00.000'
SET @EndDate = '2022-02-09 00:00:00.000'
/*
This query should be run on the AW_DB.
Make sure to update 'ucce_hds' reference in the FROM section with the actual instance name
*/

SELECT
rcd.DialedNumberID, dn.EnterpriseName as DialedNumber, dn.DialedNumberString
,rcd.CallTypeID, ct.EnterpriseName as CallType
,rcd.ScriptID, ms.EnterpriseName as ScriptName
,COUNT(*) as Total

FROM
Route_Call_Detail (NOLOCK) rcd
LEFT JOIN Call_Type (NOLOCK) ct ON rcd.CallTypeID = ct.CallTypeID
LEFT JOIN Dialed_Number (NOLOCK) dn ON rcd.DialedNumberID = dn.DialedNumberID
LEFT JOIN Script (NOLOCK) ON rcd.ScriptID = Script.ScriptID
LEFT JOIN Master_Script (NOLOCK) ms ON Script.MasterScriptID = ms.MasterScriptID
WHERE
rcd.DateTime >=@StartDate and rcd.DateTime <@EndDate

GROUP BY
rcd.DialedNumberID, dn.EnterpriseName, dn.DialedNumberString, rcd.CallTypeID, ct.EnterpriseName,rcd.ScriptID, ms.EnterpriseName