02-25-2022 01:46 PM - edited 02-25-2022 03:15 PM
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.
02-25-2022 03:16 PM
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
02-25-2022 03:38 PM
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.
02-25-2022 04:00 PM
Is there a possibility to append DialedNumber string to DN call type to script query?
02-25-2022 04:30 PM
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
02-25-2022 05:28 PM
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
02-26-2022 06:02 AM
Yep, just put this on the end of the query:
ORDER BY ct.EnterpriseName, dn.DialedNumberString
02-26-2022 10:37 AM
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?
03-08-2022 05:25 PM - edited 03-08-2022 05:29 PM
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/
04-13-2022 07:06 PM
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.
04-15-2022 06:07 AM
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.
02-25-2022 04:31 PM
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
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide