03-01-2018 04:54 AM
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.
Solved! Go to Solution.
03-12-2018 02:25 AM
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;
03-12-2018 02:25 AM
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;
03-15-2018 12:20 AM
Hi Meyyappan Velu,
This is great and confirms that my logic was wrong initially.
Many thanks for your great help.
Have a fantastic day.
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