Joining multiple Parent/Child records

Question

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.

Answer 1

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

Joining multiple Parent/Child records

Follow this question

Related Questions