cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
974
Views
0
Helpful
1
Replies

How to join ContactCallDetail with DialingList in the CCX db

floatingpurr
Beginner
Beginner

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

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community:

Recognize Your Peers