Vladimir Lazovic:
Hi everyone,
Can someone point me out to do the following: How can I collect AccountNumber in outbound campaigns and tie it to call results?
What I want to do is to show what are the results for every BAAccount that was dialed in campaign.
e.g.
Account result attempts
123456 voice 2
Thank you
Vlad
/*
The AW/HDS view/table Dialer_Detail has all the information you are asking about, including ...:
AccountNumber,FirstName,LastName,CallResult
Converting the CallResultCode to a readable value will require a case statement, or a custom or temp table.
Doing the countAttempts aggregate while selecting only a single result will be trickier.
Maybe can do a sub query for records that are closed, get the result code there, and then go back and count the attempts.*/
/* THIS RUNS */
select
table1.AccountNumber,table1.FirstName,table1.LastName,table1.CallResult, count(DD.RecoveryKey) AS "Attempts"
from Dialer_Detail DD
inner join
(SELECT AccountNumber,FirstName,LastName,CallResult
From Dialer_Detail DD1
WHERE DD1.CallStatusZone1 like 'C'
AND DD1.DateTime >'2012-11-05 00:00:00' --@StartDateTime
and DD1.DateTime < '2012-11-08 23:59:59' --@EndDateTime
) AS table1
on DD.AccountNumber = table1.AccountNumber
AND DD.DateTime >'2012-11-05 00:00:00' --@StartDateTime
and DD.DateTime < '2012-11-08 23:59:59' --@EndDateTime
group by table1.AccountNumber,table1.FirstName,table1.LastName,table1.CallResult
order by 5 desc
/* the inner select gives the details and result for the closed records
while the outer select does a count of attempts for those records that AARE closed.
Have to use anonymous block because the date filter is used twice. */
Contact Center Reporting 
