Abandoned calls query

[Albert.Georgy7]

Can someone help me design a sql query to get the abandoned calls while waiting on queue + the abandoned calls while ringing on Agent. I'm trying to use this query on UCCE 11.

Also it would be very beneficial to to include the caller ANI in the query and datetime.

[Bill Mungaven]

Albert,

Something like this might work. I'm in a UCCE environment with 2 AW/HDS servers for CUIC reporting. I can successfully run this query from either server. I used Microsoft SQL Server Management Studio to run the query.

use ucce_hds
select DateTime, CallDisposition, ANI
from
Termination_Call_Detail
where
DateTime >= '2017-02-03 00:00' and
DateTime <= '2017-02-03 23:00' and
CallDisposition in (1,3)
order by
DateTime

Call disposition 1 is abandoned in network, 3 is abandon ring. There are other abandon codes but these ones should meet your request.

You can find the list of abandon codes and descriptions here: http://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/ipcc_enterprise/ippcenterprise10_0_1/reference/UCCE_BK_D3D5FB15_00_1001-database-schema-guide-for-ucce/UCCE_BK_D3D5FB15_00_10-0-1-database-schema-guide-for-ucce_chapter_01...

This link is for UCCE 10 but I suspect the disposition codes descriptions haven't changed much between versions.

I hope this helps.

Bill

[Albert.Georgy7]

Dear Bill,

Thank you very much for your reply.

Your query was useful but i had to add to it Call disposition 2 as well. But this did not completely catch all abandoned calls. I made some test calls and and abandoned at different points as follows:

1. Closed the line once i was directed to the queue

2. Closed the line while listening to queue music (on hold)

3. Closed the line while agent phone is ringing

4. Closed the line while talking to agent

5. Completed the call normally and did not close the line until agent did.

I then queried the Termination_Call_Details for my number (ANI) to see the records created for these tests. I noticed that for some calls more records are created in TCD table than other calls. Below is the result call dispositions found in records associated with each test respectively:

1. 13,13 (two records each has call disposition 13)

2. 13,3,13

3. 13,2

4. 13,13,13

5. 13,13,52

Querying for call disposition 2, and 3 is good but it would only catch two types of the first three abandoned calls explained above. I'm still looking for a better query that would return all the first 3 abandoned calls as per the tests explained above.

Thanks again,

[geoff]

For calls that abandon before being routed, the easiest table by far is the Route_Call_Detail table. Look at the RouterErrorCode. A value of 448 is an abandoned call. There will be no Label for this row in the RCD.

Router Error Codes

448 = This is not a routing error. The customer leg disconnected for the call at the routing client

Regards,
Geoff

[ln33147]

Hello Geoff,

I am trying to create a report (Unique Abandoned Report) that shows only the abandoned calls where the callers didn't call again in the same day and got their calls answered. 

Abandoned Calls Query:

select ANI from Route_Call_Detail where DateTime >= '2018-09-17 00:00' and DateTime <= '2018-09-17 23:00' and RouterQueueTime > 0 and RouterErrorCode = '448'

Answered Calls Query:

select ANI from Termination_Call_Detail where DateTime >= '2018-09-17 00:00' and DateTime <= '2018-09-17 23:00' and TalkTime > 0

Unique Abandoned Query:

select ANI from Route_Call_Detail where DateTime >= '2018-09-17 00:00' and DateTime <= '2018-09-17 23:00' and RouterQueueTime > 0 and RouterErrorCode = '448' and ANI not in (select ANI from Termination_Call_Detail where DateTime >= '2018-09-17 00:00' and DateTime <= '2018-09-17 23:00' and TalkTime > 0)

The Unique Abandoned Query returns nothing. What am I missing? I appreciate your feedback.

Thank you in advance.

Lara

[CuicReportingEngineer]

hey, its quite old thread, just posting for future references.:

unique abandoned might have null in actual. You can verify the anis manually by making simple select * on RCD and TCD and comparing the abandonded ANI lists.