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! ;-)

Am I missing a very simple way of doing this?

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.

Answer 1

You'll want to look at Common Table Expressions, and especially at the Recursive options.

Pseudo code from the second link:

WITH cte_name ( column_name [,...n] )
AS
( 
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name

If you can answer the question I've added to your question, I may be able to post some code that's closer to what you'll actually need. At the moment, I'm not sure how to build the anchor query, since it's not clear how to identify the (super-) parent rows. I'd hoped it would be rows with no ICRCallKeyParent value, but your sample output doesn't support that notion.