script to script transferred calls or DN to DN transferred calls

[kavle]

Hi Team ,

I need some help in pulling the query information in a single report. 

I have a query where I can pull data for the call got blind transferred to destination script only .

below is the query I am using to pull the data for the total call transferred to a specific DN. But this one will not include the primary DN and primary AGent.
$$$$$$$$$$$$$$$$$
SELECT DISTINCT  RouterCallKeySequenceNumber, RouterCallKey, RouterCallKeyDay, EnterpriseName, CallDisposition , DateTime , DigitsDialed , NewTransaction,DNIS,ANI , Termination_Call_Detail.DateTime

FROM Termination_Call_Detail
INNER JOIN Peripheral
ON Termination_Call_Detail.PeripheralID = Peripheral.PeripheralID
WHERE DateTime BETWEEN '2024-01-01' and '2024-01-17' and CallDisposition = 28 and RouterCallKeySequenceNumber > 1
AND DigitsDialed = '9998800560'
order by Termination_Call_Detail.DateTime
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

could anyone help me with the query info - where I can pull data with details of original Script or Dailed number and destination script  or  dialed number along  with primary Agent and secondary agent details in one single report((this one we are looking for to undertand .. how many call got transferred to Repair service agents from Sales team agents, when Sales agents wants to transfer the call to Repair team , they will dial the Repair service Team DN and it will hit Repair service script)))

with the help of RCKey and RCkeyday I can pull individual call details with the complete information, but I am not getting how to pull all the details in one single report.

[Omar Deen · Accepted solution]

With the help of my colleague and some Stackoverflow, here's my best shot

DECLARE @dateFrom DATETIME
SET @dateFrom = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
;WITH RouterDetail(RCKD, RCK, ClientCallKey, ScriptName, BeganRoutingDateTime, DialedNumberString)
AS
(SELECT
RCD.RouterCallKeyDay, RCD.RouterCallKey, RCD.RoutingClientCallKey, MS.EnterpriseName ScriptName, RCD.BeganRoutingDateTime, RCD.DialedNumberString
FROM Route_Call_Detail RCD
LEFT JOIN Script S ON RCD.ScriptID = S.ScriptID
LEFT JOIN Master_Script MS ON S.MasterScriptID = MS.MasterScriptID
WHERE RCD.DateTime > @dateFrom
)
SELECT
TCD.RouterCallKeyDay, TCD.RouterCallKey,
MAX(CASE WHEN RCD.ClientCallKey = 0 THEN TCD.DigitsDialed END) AS OriginalDigitsDialed,
MAX(CASE WHEN RCD.ClientCallKey = 0 THEN P.FirstName + ' ' + P.LastName END) AS OriginalAgent,
MAX(CASE WHEN RCD.ClientCallKey = 0 THEN PQ.EnterpriseName END) AS OriginalAgentSkillGroup,
MAX(CASE WHEN RCD.ClientCallKey = 0 THEN RCD.ScriptName END) AS OriginalScript,
MAX(CASE WHEN RCD.ClientCallKey = 0 THEN RCD.BeganRoutingDateTime END) AS TransferDateTime,
MAX(CASE WHEN RCD.ClientCallKey = 0 THEN RCD.DialedNumberString END) AS TransferDestination,
MAX(CASE WHEN RCD.ClientCallKey > 0 THEN TCD.DigitsDialed END) AS XferToDigitsDialed,
MAX(CASE WHEN RCD.ClientCallKey > 0 THEN P.FirstName + ' ' + P.LastName END) AS XferToAgent,
MAX(CASE WHEN RCD.ClientCallKey > 0 THEN PQ.EnterpriseName END) AS XferToAgentSkillGroup,
MAX(CASE WHEN RCD.ClientCallKey > 0 THEN RCD.ScriptName END) AS XferToScript,
MAX(CASE WHEN RCD.ClientCallKey > 0 THEN RCD.BeganRoutingDateTime END) AS XferToTransferDateTime,
MAX(CASE WHEN RCD.ClientCallKey > 0 THEN RCD.DialedNumberString END) AS XferToExtension
FROM Termination_Call_Detail TCD
LEFT JOIN Agent A ON TCD.AgentSkillTargetID = A.SkillTargetID
LEFT JOIN Person P ON A.PersonID = P.PersonID
LEFT JOIN Precision_Queue PQ ON TCD.PrecisionQueueID = PQ.PrecisionQueueID
LEFT JOIN Skill_Group SG ON TCD.SkillGroupSkillTargetID = SG.SkillTargetID,
RouterDetail RCD
WHERE TCD.RouterCallKeyDay = RCD.RCKD
AND TCD.RouterCallKey = RCD.RCK
AND TCD.DateTime > @dateFrom
AND RCD.ClientCallKey >= 0
AND TCD.CallDisposition IN (28,29,30)
AND TCD.DateTime > @dateFrom
GROUP BY TCD.RouterCallKeyDay, TCD.RouterCallKey

[kavle]

thanks a lot for the query @Omar Deen 

I see that Query using Getdate() which will pull the current date , however I am looking to pull the report for the date range using " DATEDIFF" function , but that didn't help.

is there a way I can pull the data with the above query for specific date range . 

[Omar Deen]

You can use DATEDIFF still

Replace this

SET @dateFrom = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) 

With this

SET @dateFrom = CAST(FLOOR(CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AS FLOAT)) AS DATETIME) 

This will give you the first day of the current month. Swap in a negative value for zero to go back however many months you want. Similarly, you can replace MONTH with DAY or YEAR

[kavle]

thanks  LOT @Omar Deen . It worked .

could you help me understand whats the significance of using RCD.ClientCallKey = 0 and RCD.ClientCallKey > 0 in your query.

[Omar Deen]

Part of what makes this challenging is trying to identify what is a transferred call and how can we tie in other information as it pertains to the transferred call. I think it's important to include what script the call originally terminated at and where it was transferred to, so we need to pull in RCD for that information. The challenge there is that RCD doesn't always seem to label a call as a transfer. I was originally using the RequestType column looking for values of 2 (cold) or 3 (warm), but sometimes, it would be a 6. I didn't take a lot of time as to why, but it was obvious that I couldn't rely on that data. The only thing in the RCD table that definitely stood out as an original and transferred call was the RoutingClientCallKey. The original call always has a value of 0 while transfers had a value great than 0. What throws a wrench in my query is if there are multiple transfers... I'm not accounting for that. I'm still working on improving this query, but it's a good first go at it. If you find ways to improve it, please share with the rest of the community.

[kavle]

Thanks for the reply @Omar Deen 

I am trying to  display just two records also for each calls , one call record with callkeysequence number 0 and another with 6 , I am not getting how to group by that data with RCcallkey

[Omar Deen]

I'm not sure you can specifically look for 6 there. Maybe say greater than 0.

[kavle]

Hello @Omar Deen 

I just made little tweak on your first Query and it did work and got the business expected result sir .

MAX(CASE WHEN RCD.ClientCallKey > 0 THEN RCD.lable END) AS SecondaryEXTENSION,

above line I added for the transferred extension result and also Call dispotion line for full details.

thank you sir!!!