SQLTeam.com | Weblogs | Forums

Transfer Tracking Issue

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!

Welcome!

Please provide sample data in proper ddl dml format instead of a screen shot? Help us help you