How to join ContactCallDetail with DialingList in the CCX db

[floatingpurr]

Hello guys, I've a question about the CCX db.

I'd like to connect the DialingList table with the ContactCallDetail table to get the contact each call belongs to. At a first sight, it looks like there isn't a direct connection with those two tables. Is there a way to get the info I need?

[floatingpurr · Accepted solution]

An initial solution can be:

SELECT 
   d.accountnumber, 
   d.firstname, 
   d.lastname, 
   c.destinationdn,
   c.callednumber,
   c.startdatetime,
   c.enddatetime,
   c.callresult 
FROM contactcalldetail c 
LEFT OUTER JOIN (
                     SELECT * 
                     from dialinglist where campaignid={id} 
                     UNION ALL
                     SELECT *
                     FROM dialinglisthistory WHERE campaignid={id}) d
ON c.campaignid=d.campaignid AND c.destinationdn = d.phone01
WHERE 
      c.campaignid={id} 
AND 
      c.startdatetime between '{start_date}' and '{end_date}'

Rationale:

• the join is performed between contactcalldetail and the union of (dialinglist + dialinglisthistory). We need to consider this union if we want to deal with the Cisco purge process that moves inactive contacts from dialinglist to dialinglisthistory.
• the join rule is contactcalldetail.campaignid=dialinglist.campaignid AND contactcalldetail.destinationdn =dialinglist.phone01 that works in my case but YMMV : )
• the query harnesses a left outer join in order to return all the records from contactcalldetail regardless of the match w/ dialinglist + dialinglisthistory.

• no duplicate contacts are assumed in (dialinglist + dialinglisthistory)

Unfortunately, I haven't yet found a join strategy based on foreign keys...