Account Number in Outbound Report

Version 1
    This document was generated from CDN thread

    Created by: Vladimir Lazovic on 08-11-2012 08:08:03 AM
    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

    Subject: RE: Account Number in Outbound Report
    Replied by: Edwin Andrews on 08-11-2012 10:19:14 AM
    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. */

    Subject: RE: Account Number in Outbound Report
    Replied by: Vladimir Lazovic on 14-11-2012 02:05:17 AM
    Hi Edwin, 
    Will try definitely.
     
    Thank you Edwin.
     
    Cheers 

    V

    Subject: Automatic reply: New Message from Vladimir Lazovic in Contact Center Report
    Replied by: Nick Santilli on 14-11-2012 02:07:50 AM
    I am out of the office in Training the entire week of November 12-16 with limited access to email. However I will respond as I am able to later in the day.
    For pressing issues, please contact Colin Day (cday@msncomm.com<https://owa.msncomm.com/ecp/Organize/cday@msncomm.com//owa.msncomm.com/ecp/Organize/cday@msncomm.com>).

    Thanks
    nick santilli





    *******************************************************************************

    This message may contain confidential and/or privileged information. This
    information is intended to be read only by the individual or entity to whom it
    is addressed. If you are not the intended recipient, you are on notice that any
    review, disclosure, copying, distribution or use of the contents of this
    message is strictly prohibited. If you have received this message in error,
    please notify the sender immediately and delete or destroy any copy of this
    message.