cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
679
Views
0
Helpful
2
Replies

Transfer Tracking Issue - Cisco Unified ICM/Contact Center Enterprise

Ash_Ram
Level 1
Level 1

I am trying to track transfer in Cisco Unified ICM but have a few questions.

 

CASE 1

Data

An agent receives a call and resolves the case:

DateCallIDAgentNameDepartmentTransfer
2020-01-01123PQSmithBilling0

 

So the Transfer column here is a tag that I have created, whenever a call is transferred it is set as 1. In this case since there wasn't any transfer it is 0.

 

CASE 2

Data: Table 

An agent receives a call. He decides to transfer it to another Department and the transferred to agent resolves the case:

DateCallIDAgentNameDepartmentTransfer
2020-01-02146LMAubreyBilling1
2020-01-02146LMMistySales0

 

Required output:

DateCallIDAgentNameDepartmentTransferredToAgentTransferredToDept
2020-01-02146LMAubreyBillingMistySales

 

In order to get this I used self join,

select a.Date, a.CallID, a.AgentName, a.Department,

b.AgentName as TransferredToAgent, b.Department as TransferredToDept

from table a

join table b on a.callID = b.callID and a.Transfer = 1 and b.Transfer = 0

 

But the output of this query has a lot of duplicate values for the same record. (Multiple entries for the same record)

 

CASE 3

Data:  

Multiple transfers: An agent receives a call. He decides to transfer it to another department and this agent transfers it again to another department and the last transferred to agent resolves the case:

DateCallIDAgentNameDepartmentTransfer
2020-01-02146LMAubreyBilling1
2020-01-02146LMMistySales1
2020-01-02146LMJoshMarketing0

 

Required output:

DateCallIDAgentNameDepartmentTransferredToAgentTransferredToDept
2020-01-02146LMAubreyBillingMistySales
2020-01-02146LMMistySalesJoshMarketing

 

What would be an efficient way of retrieving the required output in both the case 2 and case 3?

 

Thanks!

 

2 Replies 2

Omar Deen
Spotlight
Spotlight
I don't understand this query, it doesn't make sense... what tables are you querying from? Is the Transfer column supposed to be a Peripheral Variable? How are you calling the Department name without joining a table with DepartmentID and calling on the Departments Enterprisename?

All the connections and joins have been performed based on the keys and I haven't included those in my question. The ultimate result i get is the Table shown in the cases. I just have to bring the transferred to agent that appears below as a column as shown in the Required output table. 

 

Ignoring the fact that it is CISCO data, how can I achieve this requirement based on the tables that I have provided (more like a SQL question).

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: