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!