I am doing some analysis on a Cisco ICM database looking specifically at transferred calls.
Basically there is an ICRCallKey field which is a unique number generated at a peripheral gateway for each line logged in the table. I can tell when a call is transferred by looking at the ICRCallKeyParent and ICRCallKeyChild fields and seeing if they contain a value in the ICRCallKey field.
Where it gets tricky is when a call has been transferred more than once because there is a value in each field as would be expected.
For example if a call has been transferred five times I would like to see each of those lines from the database so I can see the route that the call has taken. This is called cradle-to-grave (for some unknown reason!) and the call can be tracked through the different peripherals and users and the total time the whole call was in the system and so on. The possibilities are endless! ;-)
Now I know it is Monday morning but am I missing a very simple way of doing this?
Edit 1: Addition of some sample data and required output
This is the SQL that I am using and an example of the output is below.
As you can see I have a basic join on
What I would like to see is something like the following (made up) result:
Does this make more sense? I would like the CallReference to increment with each new call but the CallTransferCounter to increment with each transferred call IE there is a parent/child relationship.
I am out of the meeting I was in this morning now so ready to answer any more queries. Cheers, Ian.
I had to massage your data to fit as I understood it should. Let me explain:
I based my solution on your (made-up) results - 4 liner near the end of your post. I had to do this, because the other data was not going to deliver the made up results.
I made one change to the data. I don't know if you will be able to do this, but it is pivotal in making this solution work. I filled the ICRCallKeyParent with the parent ICRCallKey in a call chain. In the example data you can see this for CallReference 1. The parent ICRCallKey is 1879479165 which is then set for all calls in the same chain. This is used as the anchor for the row numbering function and allows you to group the calls together. Your test data left this NULL all the time, if you can change that, you should have won.
So here is the solution that delivers the expected results in your OP. The tricky part was the Call Reference. You basically create a running count per Parent, then subtract the running count of all calls. This generates a grouping number, that is then run through the DENSE_RANK() to give you the CallReference.
Give it a try and let me know if it worked.
EDIT: This is an updated version of the script. It will now generate a true ICRCallKeyParent as required by the original post. This is accomplished through the recursive CTE named "Calls". Please be aware that recursion can be very resource intensive. As far as I can see this should be quick, though it will depend upon the data size/spread in the calls table that you have (CPU intensive rather than I/O intensive).