I have a customer who wants to understand the folllowing
Call comes into Agent A, Agent A transfers the call to Agent B using a Short code that goes into a Skill group, Agent B takes that Call and finishes it.
Customer wants to report on that whole journey, this will need to be run by Skill group - Agent State trace is out of the question as it is in a call centre of over 100 agents.
What I am keen to understand how do you see to Agents name in 1 line showing that call was transfered?
Is this a case of use Route_Call Detail & Termination Call Detail tables, plus the Agent Skill group etc, or is there a simpler way, and how do I show both Agent names?
This would have to be done with reporting on the Termination_Call_Detail table. You should not need Route_Call_Detail or Agent_Skill_Group tables. A complete call should have a consistent RouterCallKey and RouterCallKeyDay across multiple rows of data. The RouterCallKeySequenceNumber will increment based on order of call leg starts, and DateTime will have the time the call leg ended. The Agent, Skill Group, and Call Type that the call delivered to are all available in TCD. I'll warn you, it can get a little tricky to decipher if calls start having many call legs, multiple transfers, transfers with RONA events, et cetera.
I've taken a stab at summarized transfer reporting before (for example, showing all destination call types based on original call type and time range for transfer), and it can get quite complex. I ended up with a report that joins TCD with itself 9 times in order to properly connect various call legs together properly in a CVP environment. I was able to write it in a way that it runs quite fast, but the SQL is not pretty.
I knew it wasn't going to be easy - this to me is a fundamental problem with the Routercall key it is almost lik looking for a needle in a haystack trying to determine the same routercall key etc....
I will take a stan at it and get back to you....
As always thanks for your help as you can probably gather I am learning on the job....