Differences

This shows you the differences between two versions of the page.

--- vendors:cisco:uc:icm:sql:outbound [2018/12/03 15:44] – gerardorourke
+++ vendors:cisco:uc:icm:sql:outbound [2021/11/10 14:34] (current) – [Campaign Table Summary Counts] gerardorourke
@@ Line 348: / Line 348: @@
 {{ vendors:cisco:uc:icm:sql:dialer_detail.zip |}}\\
-===== bA Table =====
+===== bA Campaign_QueryRule Tables =====
 note - for support use only. Do you give to end users.
+==== Pending Records ====
 <code SQL>
@@ Line 416: / Line 418: @@
   FROM [ucce_baA].[dbo].[DL_5000_5000] nolock
-  WHERE (CallStatusZone1 <> 'C' AND CallStatusZone1 <> 'J')
+  WHERE (CallStatusZone1 <> 'C' AND CallStatusZone1 <> 'J' AND CallStatusZone1 <> 'M')
   </code>
+==== Campaign Table Summary Counts ====
+<code sql>
+SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
+SELECT
+	  ImportRuleDay = CONVERT(Datetime,(CONVERT(CHAR(10),[ImportRuleDate],102)),102)
+      ,[CallResult]
+      ,CallResultDesc = CASE [CallResult]
+      WHEN '0' THEN ''
+      WHEN '2' THEN 'Dial Error'
+      WHEN '3' THEN 'Not in Service'
+      WHEN '4' THEN 'No Ringback'
+      WHEN '5' THEN 'Operator Intercept'
+      WHEN '6' THEN 'No Dial Tone'
+      WHEN '7' THEN 'Number Invalid'
+      WHEN '8' THEN 'No Answer'
+      WHEN '9' THEN 'Busy'
+      WHEN '10' THEN 'Answered'
+      WHEN '11' THEN 'Fax'
+      WHEN '12' THEN 'Answering Machine'
+      WHEN '13' THEN 'Dialer stopped due to lack of agents or network stopped dialing before it was complete'
+      WHEN '14' THEN 'Customer requested callback'
+      WHEN '16' THEN 'Call was abandoned by the dialer due to lack of agents'
+      WHEN '17' THEN 'Failed to reserve agent for personal callback.'
+      WHEN '18' THEN 'Agent has skipped or rejected a preview call or personal callback call.'
+      WHEN '19' THEN 'Agent has skipped or rejected a preview call with the close option'
+      WHEN '20' THEN 'Customer has been abandoned to an IVR'
+      WHEN '21' THEN 'Customer dropped call within configured abandoned time'
+      WHEN '22' THEN 'Network Answering Machine'
+      WHEN '23' THEN 'Number successfully contacted but wrong number'
+      WHEN '24' THEN 'Number successfully contacted but reached the wrong person'
+      WHEN '25' THEN 'Dialer has flushed this record due to a change in the skillgroup, the campaign, etc.'
+      WHEN '26' THEN 'The number was on the do not call list'
+      WHEN '27' THEN 'Call disconnected by the carrier or the network while ringing'
+      WHEN '28' THEN 'Data error or no-value call e.g. no voice detected'
+      ELSE CAST(CallResult AS Varchar(200))
+      END
+      ,[CallStatusZone1]
+      ,CallStatusDesc = CASE [CallStatusZone1]
+      WHEN 'A' THEN 'Active'
+      WHEN 'B' THEN 'Callback'
+      WHEN 'C' THEN 'Closed'
+      WHEN 'J' THEN 'Agent Rejected'
+      WHEN 'M' THEN 'Maximum Attempts Reached'
+      WHEN 'P' THEN 'Pending'
+      WHEN 'R' THEN 'Retry'
+      WHEN 'S' THEN 'Personal Callback'
+      WHEN 'U' THEN 'Unknown'
+      WHEN 'X' THEN 'Personal Callback Abandoned'
+      ELSE CAST(CallStatusZone1 AS VarChar(200))
+      END
+	  ,Count = Count(*)
+  FROM [ucce_baA].[dbo].[DL_5054_5055] nolock
+WHERE ImportRuleDate >= :start_date AND ImportRuleDate < :end_date
+Group by CONVERT(Datetime,(CONVERT(CHAR(10),[ImportRuleDate],102)),102), CallResult, CallStatusZone1
+  ORDER BY ImportRuleDay, CallStatusZone1
+</code>