CUIC Ones!

This is an old revision of the document!

UCCE Outbound SQL Queries

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--USE <cust_awdb>

SELECT     
	Dialer_Detail.DateTime,
--Dialer_Detail.DbDateTime,
	Campaign.CampaignName, 
--Dialer_Detail.CampaignID AS CampaignID,
Query_Rule.QueryRuleName, 
--Dialer_Detail.QueryRuleID AS QueryRuleID, 
Import_Rule.ImportRuleName, 
--Query_Rule.ImportRuleID, 
--Import_Rule.FilePath, 
--Dialer_Detail.DialingListID AS Campaign_QueryRule_DialingListID, 
	Dialer_Detail.Phone AS Phone, 
--Dialer_Detail.CallResult AS CallResult,
      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 AS 'CallResultString',

--Dialer_Detail.CallStatusZone1 AS CallStatusID,

      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 AS 'CallStatus',

--Dialer_Detail.SkillGroupSkillTargetID,
Skill_Group.EnterpriseName,
--Dialer_Detail.AgentPeripheralNumber,
--Dialer_Detail.PeripheralID,
AgentEnterpriseName = isNULL(Agent.EnterpriseName,'NOT ROUTED TO AGENT'),
Dialer_Detail.AccountNumber,
Dialer_Detail.FirstName,
Dialer_Detail.LastName,
Dialer_Detail.CallbackPhone,
Dialer_Detail.CallbackDateTime,
--Dialer_Detail.DialingMode,
	CASE [DialingMode]
	WHEN '1' THEN 'Predictive Only'
	WHEN '2' THEN 'Predictive Blended'
	WHEN '3' THEN 'Preview Only'
	WHEN '4' THEN 'Preview Blended'
	WHEN '5' THEN 'Progressive Only'
	WHEN '6' THEN 'Progressive Blended'
	WHEN '7' THEN 'Direct Preview Only'
	WHEN '8' THEN 'Direct Preview Blended'
	ELSE CAST(DialingMode AS VarChar(200))
	END AS DialedModeString,
Dialer_Detail.RouterCallKey,
Dialer_Detail.RouterCallKeyDay,
Dialer_Detail.ReservationCallDuration/1000 AS ReservationCallDurationSec,
Dialer_Detail.PreviewTime/1000 AS PreviewTimeSec,
Dialer_Detail.CallDuration/1000 AS CallDurationSec


FROM         
Dialer_Detail 
INNER JOIN Campaign ON Dialer_Detail.CampaignID = Campaign.CampaignID
INNER JOIN Query_Rule ON Dialer_Detail.QueryRuleID = Query_Rule.QueryRuleID
INNER JOIN Import_Rule ON Query_Rule.ImportRuleID = Import_Rule.ImportRuleID
INNER JOIN Skill_Group ON Dialer_Detail.SkillGroupSkillTargetID = Skill_Group.SkillTargetID
LEFT JOIN Agent ON Dialer_Detail.AgentPeripheralNumber = Agent.PeripheralNumber
                      
WHERE 
Dialer_Detail.DateTime > GETDATE()-1

ORDER BY Dialer_Detail.DateTime DESC

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT     				
--Dialer_Detail.DateTime, 				
--Dialer_Detail.DbDateTime, 				
Campaign.CampaignName, 				
Dialer_Detail.DialingMode,				
	CASE [DialingMode]			
	WHEN '1' THEN 'Predictive Only'			
	WHEN '2' THEN 'Predictive Blended'			
	WHEN '3' THEN 'Preview Only'			
	WHEN '4' THEN 'Preview Blended'			
	WHEN '5' THEN 'Progressive Only'			
	WHEN '6' THEN 'Progressive Blended'			
	WHEN '7' THEN 'Direct Preview Only'			
	WHEN '8' THEN 'Direct Preview Blended'			
	ELSE CAST(DialingMode AS VarChar(200))			
	END AS DialedModeString,			
				
				
Dialer_Detail.CallResult AS CallResult,				
     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 AS 'CallResultString',				
--Dialer_Detail.CallStatusZone1,				
Count(*) As COUNT				
				
				
FROM         				
Dialer_Detail,				
Campaign				
				
				
				
WHERE DateTime > '2014-11-03 00:00:00' 				
AND DateTime < '2014-11-04 00:00:00'				
				
--AND CampaignName like 'blah'				
AND Dialer_Detail.CampaignID = Campaign.CampaignID				
				
Group BY CampaignName,CallResult,DialingMode				
ORDER BY CampaignName,DialedModeString,CallResult

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT
Phone, 
CallResult, 
CampaignName,
RouterCallKeyDay,
RouterCallKey
 
FROM         				
Dialer_Detail,				
Campaign				
 
WHERE DateTime > '2014-11-03 00:00:00' 				
AND DateTime < '2014-11-03 23:59:59'
 
AND (CallResult='4' OR CallResult ='7')
AND CampaignName LIKE 'BLAH%'	
AND Dialer_Detail.CampaignID = Campaign.CampaignID	
ORDER BY Phone

Reservation Calls

SELECT 
StartDateTimeUTC,
DateTime,
AgentSkillTargetID,
AgentPeripheralNumber,
InstrumentPortNumber,
DNIS,
SkillGroupSkillTargetID,
CED,
ANI,
Duration,
HoldTime,
RouterCallKeyDay,
RouterCallKey,
ICRCallKey,
DigitsDialed,
PeripheralCallKey,
CallDisposition
 
FROM Termination_Call_Detail
WHERE Termination_Call_Detail.DateTime > '2014-11-03 00:00:00'
AND DateTime < '2014-11-03 23:59:59'
AND AgentSkillTargetID = '12345'
AND CED = 'ICM_BA_Reservation_Call'
ORDER BY DateTime

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


SELECT     
    DateTime = Dialer_Detail.DateTime,
    DbDatetime = Dialer_Detail.DbDateTime,
	CampaignName = Campaign.CampaignName, 
    CampaignID = Dialer_Detail.CampaignID,
    QueryRuleName = Query_Rule.QueryRuleName, 
    QueryRuleID = Dialer_Detail.QueryRuleID, 
    ImportRuleName = Import_Rule.ImportRuleName, 
	ImportRuleID = Query_Rule.ImportRuleID , 
    FilePath = Import_Rule.FilePath,  
    DialingListID = Dialer_Detail.DialingListID, 
    Phone = Dialer_Detail.Phone, 
    SkillGroupTargetID = Dialer_Detail.SkillGroupSkillTargetID,
	SkillGroupEnterpriseName = Skill_Group.EnterpriseName,
	AgentPeripheralID = Dialer_Detail.AgentPeripheralNumber,
	AgentEnterpriseName = isNULL(Agent.EnterpriseName,'NOT ROUTED TO AGENT'),
	AccountNumber = Dialer_Detail.AccountNumber,
	FirstName = Dialer_Detail.FirstName,
	LastName = Dialer_Detail.LastName,
	CallbackPhone = Dialer_Detail.CallbackPhone,
	CallbackDateTime = Dialer_Detail.CallbackDateTime,	
	RouterCallKey = Dialer_Detail.RouterCallKey,
	RouterCallKeyDay = Dialer_Detail.RouterCallKeyDay,
	ReservationCallDurationSec = Dialer_Detail.ReservationCallDuration/1000,
	PreviewTimeSec = Dialer_Detail.PreviewTime/1000,
	CallDurationSec = Dialer_Detail.CallDuration/1000,	
    CallResultID = Dialer_Detail.CallResult,	  
        CallResultString = CASE Dialer_Detail.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,
	CallStatusID = Dialer_Detail.CallStatusZone1,
        CallStatusString = CASE Dialer_Detail.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,
	DialingModeID = Dialer_Detail.DialingMode,
	DialingModeString = CASE Dialer_Detail.DialingMode
	WHEN '1' THEN 'Predictive Only'
	WHEN '2' THEN 'Predictive Blended'
	WHEN '3' THEN 'Preview Only'
	WHEN '4' THEN 'Preview Blended'
	WHEN '5' THEN 'Progressive Only'
	WHEN '6' THEN 'Progressive Blended'
	WHEN '7' THEN 'Direct Preview Only'
	WHEN '8' THEN 'Direct Preview Blended'
	ELSE CAST(DialingMode AS VarChar(200)) END

FROM         
Dialer_Detail 
INNER JOIN Campaign ON Dialer_Detail.CampaignID = Campaign.CampaignID
INNER JOIN Query_Rule ON Dialer_Detail.QueryRuleID = Query_Rule.QueryRuleID
INNER JOIN Import_Rule ON Query_Rule.ImportRuleID = Import_Rule.ImportRuleID
INNER JOIN Skill_Group ON Dialer_Detail.SkillGroupSkillTargetID = Skill_Group.SkillTargetID
LEFT JOIN Agent ON Dialer_Detail.AgentPeripheralNumber = Agent.PeripheralNumber
                      
WHERE 
Dialer_Detail.DateTime > GETDATE()-3650

ORDER BY Dialer_Detail.DateTime DESC

CUIC - Dialer Detail Report dialer_detail.zip

note - for support use only. Do you give to end users.

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT 
       [DialingListID]
      ,[Phone01]
      ,[CallbackNumber]
      ,[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 
      ,[LastZoneDialed]
      ,[LastNumberDialedZone1]
      ,[CallsMadeToZone1]
      ,[CallbackDateTimeZone1]
      ,[CallbackDateTime01]
      ,[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
      ,[AccountNumber]
      ,[LastName]
      ,[FirstName]
      ,[ImportRuleDate]
 
  FROM [ucce_baA].[dbo].[DL_5000_5000] nolock
  WHERE (CallStatusZone1 <> 'C' AND CallStatusZone1 <> 'J')

UCCE Outbound SQL Queries

Dialer Detail Query

Dialer Detail group by Call Outcome

Numbers By Call Result

Reservation Calls

CUIC Ones!

Dialer Detail

bA Table