Help with Route Call Detail SQL

[fwhodits3]

First I am newbie at using SQL. I am trying to see more detailed information for a call coming in. We are getting complains that calls drop.  I'm running this:

SELECT RouterCallKeyDay, RouterCallKey, ANI, DialedNumberString, RouterErrorCode, CED, ScriptID, DateTime
FROM Route_Call_Detail
WHERE DateTime >= '2020-01-01'
AND ANI LIKE '9379743368'

I get results but i am looking to get more detail from my query.

  Just looking for some help or suggestions on how to improve this query.  Why don't I see the option they selected under CED. 

[Ramamoorthy Shanmugam]

In RCD check the below information . you can find final object ID for simple troubleshooting in ICM script. If call is failing after agent transfer you need to check CVP and jgw logs.

RouterCallKeyDay=123xx

RouterCallKey=45xxx

Routing client ID : CVP Call server routing client

ANI : customer number

CED : customer enter digit if no digit it will be null

Final object ID : ICM script final node ( If call is route to SG or other node)

Dialed number string : incoming ICM DNIS

Take the RCD routecallkeyday & Routercallkey and run it for TCD.

Select * from Termination_Call_Detail where DateTime between '2019-01-05 00:00:00' and '2019-01-05 23:59:59' and  RouterCallKeyDay=123xx and RouterCallKey=45xxx order by DateTime desc

In TCD check the below

Duration , Ring time , Delaytime, Timeto aband , and talktime

RoutercallkeysequeanceNumber , CED , Instrumentportnumber and AgentPeripheralnumber.

Ram.S

[Marek (gaman-gt.com)]

Hi fwhodits3,

Retrieving data from Route_Call_Detal (RCD) or Termination_Call_Deatail (TCD) is not an easy task. Simple query will not give you clear results. The problem is complex but here are some tips that you can use:

1. When you want to track the call, it is better to use the TCD instead of RCD.
2. Each incoming call receives its individual id that is represented in RCD and TCD with two columns: RouterCallKeyDay – numeric identifier of the day and RouterCallKey – numeric identifier of a call on daily basis.
3. Single call have multiple entries in RCD and TCD tables. Data for individual call can be pulled using the following query:

   SELECT * FROM Route_Call_Detail WHERE RouterCallKey = “XXXX” AND RouterCallKeyDay = “YYYY” ORDER BY RouterCallKeySequenceNumber

4. The data retrieved is sorted against RouterCallKeySequenceNumber which indicates the each phase of the call. If the system is PCCE then the number will indicates TCD that initiated the route request.
5. The RouterCallKeySequenceNumber = 0 is genarted as a last entry, it contains something that we understand as a call summary.

As I saw you were looking for the value of CED. If you are using ICM scripts to build and IVR then it’s possible that the values will be populated. If not I would suggest to add a CVP micro-application with type CAP that will create additional entry in TCD table which should contain CED. (If not then before CAP write the user choice to one of the Peripheral Variables)

If you are using CVP then definitely the CED will not be written to DB as DTMF’s are captured on CVP side. In this case the only option to retrieve CED is to use CVP Reporting Server.

At the end, here are 2 queries that you can use pull all the call entries with specific ANI and date for both RCD and TCD tables.

RCD

SELECT
       RouterCallKeyDay, RouterCallKey, RouterCallKeySequenceNumber, ANI, DialedNumberString, RouterErrorCode, CED, ScriptID, DateTime
FROM
       Route_Call_Detail
WHERE
       CONVERT(NVARCHAR, RouterCallKeyDay) + '|' + CONVERT(NVARCHAR, RouterCallKey) IN
       (
             SELECT
                    CONVERT(NVARCHAR, RouterCallKeyDay) + '|' + CONVERT(NVARCHAR, RouterCallKey)
             FROM Route_Call_Detail
             WHERE
                    DateTime >= '2020-01-01'
                    AND ANI LIKE '9379743368'
)
ORDER BY
       RouterCallKeyDay, RouterCallKey, RouterCallKeySequenceNumber ASC

TCD

SELECT
       RouterCallKeyDay, RouterCallKey, RouterCallKeySequenceNumber, ANI, CED, DateTime
FROM
       Termination_Call_Detail
WHERE
       CONVERT(NVARCHAR, RouterCallKeyDay) + '|' + CONVERT(NVARCHAR, RouterCallKey) IN
       (
             SELECT
                    CONVERT(NVARCHAR, RouterCallKeyDay) + '|' + CONVERT(NVARCHAR, RouterCallKey)
             FROM
                    Termination_Call_Detail
             WHERE
                    DateTime >= '2020-01-01'
                    AND ANI LIKE '9379743368'
       )
ORDER BY RouterCallKeyDay, RouterCallKey, RouterCallKeySequenceNumber ASC