cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
275
Views
0
Helpful
1
Replies
floatingpurr
Beginner

How to join ContactCallDetail with DialingList in the CCX db

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?

1 ACCEPTED SOLUTION

Accepted Solutions
floatingpurr
Beginner

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...

View solution in original post

1 REPLY 1
floatingpurr
Beginner

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...

View solution in original post

Content for Community-Ad

Spotlight Awards 2021