Cisco UCCE Outbound Option Find Query Rule for Dialed Record

October 2, 2010

Here’s a simple query using an inner join to find out which query rule added a certain record to the dialer.  This is pretty useful when you’re trying to figure out why a record was dialed which went to the wrong skill group.  The only challenge here is joining two different tables in two different databases, in this case the HDS and AW databases.

 

SELECT [DateTime]
      ,[CustomerTimeZone] AS CustTimeZone
      ,[CampaignID]
      ,[CallResult]
      ,[CallStatusZone1]
      ,[CallStatusZone2]
      ,[asp_awdb].[dbo].[t_Query_Rule].QueryRuleName
      ,[DialingListID]
      ,[Phone]
      ,[SkillGroupSkillTargetID] AS SGID
      ,[AgentPeripheralNumber] AS AgentExt
      ,[PeripheralCallKey]
      ,[CallDuration]
      ,[AccountNumber]
      ,[FirstName]
      ,[LastName]
      ,[CallbackPhone]
      ,[CallbackDateTime]
      ,[DialingMode]
      ,[DialerID]
      ,[ImportRuleDateTime]
  FROM [dam_hds].[dbo].[t_Dialer_Detail]
INNER JOIN [dam_awdb].[dbo].[t_Query_Rule] on [dam_hds].[dbo].[t_Dialer_Detail].QueryRuleID = [dam_awdb].[dbo].[t_Query_Rule].QueryRuleID
WHERE DateTime > ’10/01/2010′
ORDER BY DateTime DESC

Presto!

~david

tags: , ,
posted in Cisco, Technology by dmacias

Follow comments via the RSS Feed | Leave a comment | Trackback URL

3 Comments to "Cisco UCCE Outbound Option Find Query Rule for Dialed Record"

  1. edsand wrote:

    I am using enterprise rather than express and found this tidbit really useful.

    If you use the VIEWS from the AW, instead of the TABLES (t_XX), the views transparently combine both the AW and HDS databases.

    For example instead of referring to
    [dam_hds].[dbo].[t_Dialer_Detail]
    use instead
    [dam_awdb].[dbo].[Dialer_Detail]

    The VIEW “Dialer_Detail” in awdb abstracts the distinction between the TABLES on the awdb and the hds, simplifying things substantially.

    I tweaked your query using VIEWs and ALIASES for them, and pasted my version below.

    use [dam_awdb] –? or is it [asp_awdb]
    SELECT [DateTime]
    ,[CustomerTimeZone] AS CustTimeZone
    ,[CampaignID]
    ,[CallResult]
    ,[CallStatusZone1]
    ,[CallStatusZone2]
    ,[QueryRuleName]
    ,[DialingListID]
    ,[Phone]
    ,[SkillGroupSkillTargetID] AS SGID
    ,[AgentPeripheralNumber] AS AgentExt
    ,[PeripheralCallKey]
    ,[CallDuration]
    ,[AccountNumber]
    ,[FirstName]
    ,[LastName]
    ,[CallbackPhone]
    ,[CallbackDateTime]
    ,[DialingMode]
    ,[DialerID]
    ,[ImportRuleDateTime]
    FROM [Dialer_Detail] DD
    INNER JOIN [Query_Rule] QR
    ON DD.QueryRuleID = QR.QueryRuleID
    WHERE DateTime >= ‘2012-02-09’
    and QR.QueryRuleID < 5012
    ORDER BY DateTime DESC

  2. edsand wrote:

    …and Thank you, BTW!

  3. dmacias wrote:

    Thanks for the info and appreciate you adding to the public knowledge pool!

    ~david

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org