03-03-2020 01:46 AM
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?
Solved! Go to Solution.
03-03-2020 03:32 AM
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:
no duplicate contacts are assumed in (dialinglist + dialinglisthistory)
Unfortunately, I haven't yet found a join strategy based on foreign keys...
03-03-2020 03:32 AM
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:
no duplicate contacts are assumed in (dialinglist + dialinglisthistory)
Unfortunately, I haven't yet found a join strategy based on foreign keys...
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: