by dmacias

Cisco UCCE Outbound Option Find Query Rule for Dialed Record

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

3 Comments

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

Leave a Reply