Can someone please explain me how does sp_csq_interval consider handled /abandoned calls?

[victor.ortizDD]

Hello community,

I have been looking through the database schema guide and uccx historical documentation but I have found nothing about my problem.

I just want to know how does sp_csq_interval defines what is an handled or abandoned call.

Here is a simple query to obtain all calls in direction of VKS csq:

SELECT

*

FROM

CONTACTCALLDETAIL ccd

left join contactqueuedetail cqd

on ccd.sessionid=cqd.sessionid

left join contactservicequeue csq on cqd.targetid=csq.recordid

left join AGENTCONNECTIONDETAIL acd on ccd.sessionid=acd.sessionid

where    ccd.startdatetime between '2018-02-21 00:20:35.749' and '2018-02-21 23:59:59.749'

and ccd.applicationname like 'VKS'

and csqname like 'VKS'

My problem is that the sp_csq_interval does make specific calculation for defining handled calls (contactdisposition=2) or abandoned (contactdisposition=1 ) but with the result of my query I never get the same amount. It looks like that supplementary conditions are filtering more calls and therefore I have some differences. Is there someone how now what the conditions used by this stored_procedure knowing that this one is also using the getcsqdatainterval stored procedure ? The problem is that I can't acces this storedprocedure.

Many thanks for your support.

[mevelu · Accepted solution]

Hi,

Below are the steps used in the stored procedure to calculate the handled calls and abandoned calls.

Approach to get 'handled calls': 

Step 1:

First, get the sessionid, sessionseqnum, profileid, nodeid, qindex from Agentconnectiondetail into temp table by using the following condition

       STORE the SESSIONID, SESSIONSEQNUM, PROFILEID, NODEID, QINDEX into TEMP TABLE

       FROM CONTACTCALLDETAIL ccdr, AGENTCONNECTIONDETAIL acdr

       WHERE ccdr.sessionid = acdr.sessionid AND

             ccdr.sessionseqnum = acdr.sessionseqnum AND

             ccdr.profileid = acdr.profileid AND

             ccdr.nodeid = acdr.nodeid AND

             ccdr.startdatetime BETWEEN <<INPUT PARAMETER:STARTTIME >> AND <<INPUT PARAMETER:ENDTIME>> AND

             acdr.talktime > 0;

Step 2:

Now get the handled count by using the above temp table and Contactqueuedetail. (Note:get the SELECTED_CSQS from Contactservicequeue  based on the input parameter)

  GET COUNT(CQDR.SESSIONID)

  FROM CONTACTQUEUEDETAIL cqdr, TEMP_TABLE_ACDR tacdr, SELECTED_CSQS sc

       WHERE cqdr.sessionid = tacdr.sessionid AND

             cqdr.sessionseqnum = tacdr.sessionseqnum AND

             cqdr.profileid = tacdr.profileid AND

             cqdr.nodeid = tacdr.nodeid AND

             cqdr.qindex = tacdr.qindex AND

             cqdr.targettype = 0 AND

             cqdr.targetid = sc.csqrecordid AND

             cqdr.profileid = sc.profileid AND

             cqdr.disposition = 2

       GROUP BY cqdr.targetid, cqdr.profileid;

--------------------------------------- 

Approach to get 'Anandoned calls':

Step 1:

       STORE TARGETID, PROFILEID, METSERVICELEVEL INTO TEMP TABLE

       FROM CONTACTQUEUEDETAIL cqdr, CONTACTCALLDETAIL ccdr, SELECTED_CSQS sc

       WHERE cqdr.sessionid = ccdr.sessionid AND

             cqdr.sessionseqnum = ccdr.sessionseqnum AND

             cqdr.profileid = ccdr.profileid AND

             cqdr.nodeid = ccdr.nodeid AND

             ccdr.startdatetime BETWEEN <<INPUT PARAMETER:STARTTIME >> AND <<INPUT PARAMETER:ENDTIME>>  AND

             cqdr.disposition = 1 AND --Abandoned

             cqdr.targettype = 0 AND

             cqdr.targetid = sc.csqrecordid AND

             cqdr.profileid = sc.profileid;

Step 2:  Now take the count of metservicelevel to get the abandoned calls.

   GET csqrecordid, profileid, Count(metservicelevel)

    FROM TEMP TABLE

    GROUP BY csqrecordid, profileid;

[victor.ortizDD]

Hi Meyyappan Velu,

This is great and confirms that my logic was wrong initially.

Many thanks for your great help.

Have a fantastic day.