Transfer Tracking Issue - Cisco Unified ICM/Contact Center Enterprise

[Ash_Ram]

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:

Date	CallID	AgentName	Department	Transfer
2020-01-01	123PQ	Smith	Billing	0

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:

Date	CallID	AgentName	Department	Transfer
2020-01-02	146LM	Aubrey	Billing	1
2020-01-02	146LM	Misty	Sales	0

Required output:

Date	CallID	AgentName	Department	TransferredToAgent	TransferredToDept
2020-01-02	146LM	Aubrey	Billing	Misty	Sales

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:

Date	CallID	AgentName	Department	Transfer
2020-01-02	146LM	Aubrey	Billing	1
2020-01-02	146LM	Misty	Sales	1
2020-01-02	146LM	Josh	Marketing	0

Required output:

Date	CallID	AgentName	Department	TransferredToAgent	TransferredToDept
2020-01-02	146LM	Aubrey	Billing	Misty	Sales
2020-01-02	146LM	Misty	Sales	Josh	Marketing

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

Thanks!

[Omar Deen]

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?

[Ash_Ram]

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).