asked
Joining multiple Parent/Child records
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** SELECT p.AgentSkillTargetID [p_AgentSkillTargetID] , p.CallDisposition [p_CallDisposition] , p.DateTime [p_DateTime] , p.Duration [p.Duration] , p.ICRCallKey [p.ICRCallKey] , p.ICRCallKeyParent [p_ICRCallKeyParent] , p.ICRCallKeyChild [p.ICRCallKeyChild] , p.CallTypeID [p_CallTypeID] , c.AgentSkillTargetID [c_AgentSkillTargetID] , c.CallDisposition [c_CallDisposition] , c.DateTime [c_DateTime] , c.Duration [c_Duration] , c.ICRCallKey [c_ICRCallKey] , c.ICRCallKeyParent [c_ICRCallKeyParent] , c.ICRCallKeyChild [c_ICRCallKeyChild] , c.CallTypeID [c_CallTypeID] FROM tblTCD [p] LEFT JOIN tblTCD [c] ON p.ICRCallKeyChild = c.ICRCallKey AND p.RouterCallKeyDay = c.RouterCallKeyDay This is the SQL that I am using and an example of the output is below. p_AgentSkillTargetID p_CallDisposition p_DateTime p.Duration p.ICRCallKey p_ICRCallKeyParent p.ICRCallKeyChild p_CallTypeID c_AgentSkillTargetID c_CallDisposition c_DateTime c_Duration c_ICRCallKey c_ICRCallKeyParent c_ICRCallKeyChild c_CallTypeID -------------------- ----------------- ----------------------- ----------- ------------ ------------------ ----------------- ------------ -------------------- ----------------- ----------------------- ----------- ------------ ------------------ ----------------- ------------ 90277 29 2010-08-16 08:26:58.113 78 1879479165 NULL 1879479175 7669 94669 30 2010-08-16 02:54:04.077 499 1879479175 NULL 1879479179 15029 90045 28 2010-08-16 08:58:27.623 98 1879479460 NULL 1879479480 7890 104415 28 2010-08-16 08:42:27.067 43 1879479480 NULL 1879479481 15029 89971 29 2010-08-16 09:10:53.110 586 1879479523 NULL 1879479628 7663 97518 29 2010-08-16 09:19:04.583 109 1879479628 NULL 1879479650 23893 74814 28 2010-08-16 09:05:08.577 115 1879479174 NULL 1879479238 19256 92707 7 2010-08-16 08:33:50.103 2 1879479238 NULL NULL 7663 80435 28 2010-08-16 09:04:52.577 103 1879479171 NULL 1879479194 19263 94669 30 2010-08-16 04:14:33.077 121 1879479194 NULL 1879479198 15029 88952 29 2010-08-16 09:05:24.033 83 537702168 NULL 537702175 26543 54070 28 2010-08-16 09:43:32.597 784 537702175 NULL 537702344 16016 74783 28 2010-08-16 09:14:11.080 363 1879479324 NULL 1879479379 19856 102341 29 2010-08-16 09:19:27.600 1859 1879479379 NULL 1879479809 7669 89161 29 2010-08-16 09:10:45.540 151 537702198 NULL 537702212 16094 103369 29 2010-08-16 09:40:35.593 412 537702212 NULL 537702257 25507 74708 29 2010-08-16 09:20:09.083 707 1879479331 NULL 1879479487 10216 99954 7 2010-08-16 08:58:50.623 2 1879479487 NULL NULL 7663 100868 29 2010-08-16 09:10:43.540 113 537702204 NULL 537702219 26543 70678 29 2010-08-16 09:36:46.590 55 537702219 NULL 537702226 20067 As you can see I have a basic join on `p.ICRCallKeyChild = c.ICRCallKey`. If the call is transferred again then there will be a number in c_ICRCallKeyChild which would then join to tblTCD in the same way as the first join. What I would like to see is something like the following (made up) result: CallReference CallTransferCounter AgentSkillTargetID CallDisposition DateTime Duration CallTypeID ------------- ------------------- ------------------ --------------- ----------------------- -------- ---------- 1 1 90277 29 2010-08-06 08:26:58.113 78 7699 1 2 90045 30 2010-08-06 08:28:56.445 345 5467 1 3 7786 13 2010-08-06 08:34:34.243 445 4355 2 1 78973 13 2010-08-06 09:14:34.423 43 3342 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.
And also a sample of the output you're aiming at? I've done similar stuff before, but on an ad-hoc basis, so I *think* I know what you're after, but it would be nice to be sure!
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).** /* Create Test Data */ DECLARE @TestTable TABLE (AgentSkillTargetID int, CallDisposition int, DateTime datetime, Duration int, ICRCallKey int, ICRCallKeyParent int, ICRCallKeyChild int, CallTypeID int) INSERT @TestTable SELECT 90277,29,'2010-08-06 08:26:58.113',78,1879479165,NULL,1879479175,7669 UNION ALL SELECT 7786,13,'2010-08-06 08:34:34.243',445,1879479185,NULL,NULL,4355 UNION ALL SELECT 90045,30,'2010-08-06 08:28:56.445',345,1879479175,NULL,1879479185,5467 UNION ALL SELECT 78973,13,'2010-08-06 09:14:34.423',43,1879479195,NULL,NULL,3342 ; WITH Calls /* Recursive CTE to generate the ParentKey information that is missing from the imported data. */ AS (/* First part finds the parent calls (anything with no parent itself) and generates the anchor data-set for the recursion */ SELECT AgentSkillTargetID, CallDisposition, DateTime, Duration, ICRCallKey, ICRCallKeyParent = ICRCallKey, ICRCallKeyChild, CallTypeID FROM @TestTable parent WHERE NOT EXISTS ( SELECT * FROM @TestTable prev WHERE parent.ICRCallKey = prev.ICRCallKeyChild ) UNION ALL /* Second part now joins all call data from the base table with the anchor data to find the children. Note that the ICRCallKeyParent is basically a constant (the anchor parent)*/ SELECT children.AgentSkillTargetID, children.CallDisposition, children.DateTime, children.Duration, children.ICRCallKey, parent.ICRCallKeyParent, children.ICRCallKeyChild, children.CallTypeID FROM @TestTable children INNER JOIN Calls parent ON parent.ICRCallKeyChild = children.ICRCallKey) /* Using the data from the recursive CTE, we generate a CallTransferCount by running a rowcount per ICRCallKeyParent To generate the CallReference, we need to first identify the calls in groups (CallGroup). This done by subtracting the rowcounter of all rows from the CallTransferCount. To finish the CallReference generation, a DENSE_RANK of the CallGroup in descending order ensures CallReference starts at 1 and is assigned cleanly from there on. */ SELECT CallReference = DENSE_RANK() OVER (ORDER BY CallGroup DESC), CallTransferCount, AgentSkillTargetID, CallDisposition, DateTime, Duration, CallTypeId FROM (SELECT CallGroup = ROW_NUMBER() OVER (PARTITION BY ICRCallKeyParent ORDER BY ICRCallKeyChild) - ROW_NUMBER() OVER (ORDER BY ICRCallKeyParent, ICRCallKeyChild), CallTransferCount = ROW_NUMBER() OVER (PARTITION BY ICRCallKeyParent ORDER BY ICRCallKeyChild), AgentSkillTargetID, CallDisposition, DateTime, Duration, CallTypeId FROM Calls) CallStack
This is fantastic William thank you very much. I can see where you are coming from regarding the ICRCallKeyParent but unfortunately the data isn't set up that way. It appears to randomly contain values that don't make much sense. Would I be able to massage the data to get it in the format that you did to make the query work?
