UCCE SQL

Note: Prefix all report SQL with

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

Last 1000 Calls

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT TOP (1000)
 
StartDateTime = Route_Call_Detail.DateTime, 
MRDomainID = Route_Call_Detail.MRDomainID,
MRDomainName = Media_Routing_Domain.EnterpriseName,
DBDateTime = Route_Call_Detail.DbDateTime, 
ANI = ISNULL(Route_Call_Detail.ANI,'NULL'), 
DialedNumberString = Route_Call_Detail.DialedNumberString, 
RoutingClientID = Route_Call_Detail.RoutingClientID, 
RoutingClientName = Routing_Client.EnterpriseName,
CallTypeName = Call_Type.EnterpriseName,
MasterScriptName = Master_Script.EnterpriseName,
FinalObjectID = Route_Call_Detail.FinalObjectID, 
Label = Route_Call_Detail.Label, 
RouterCallKeySequenceNumber = [RouterCallKeySequenceNumber],
RouterQueueTime = [RouterQueueTime],
RecoveryKey = Route_Call_Detail.RecoveryKey, 
RouterCallKeyDay = Route_Call_Detail.RouterCallKeyDay, 
RouterCallKey = Route_Call_Detail.RouterCallKey, 
VruScripts = Route_Call_Detail.VruScripts, 
BeganRoutingDateTime = Route_Call_Detail.BeganRoutingDateTime, 
BeganCallTypeDateTime = Route_Call_Detail.BeganCallTypeDateTime, 
TargetType = Route_Call_Detail.TargetType, 
RequeryResult = Route_Call_Detail.RequeryResult, 
VruProgress = Route_Call_Detail.VruProgress, 
RouterErrorCode = Route_Call_Detail.RouterErrorCode,
RouteID = RouteID,
RequestType = RequestType,
Var1 = Route_Call_Detail.Variable1,
Var2 = Route_Call_Detail.Variable2,
Var3 = Route_Call_Detail.Variable3,
Var4 = Route_Call_Detail.Variable4,
Var5 = Route_Call_Detail.Variable5,
Var6 = Route_Call_Detail.Variable6,
Var7 = Route_Call_Detail.Variable7,
Var8 = Route_Call_Detail.Variable8,
Var9 = Route_Call_Detail.Variable9,
Var10 = Route_Call_Detail.Variable10,
UserToUser = UserToUser,
CDPD = CDPD,
CED = CED
 
FROM 
Route_Call_Detail(nolock), 
Script(nolock), 
Master_Script(nolock), 
Call_Type(nolock), 
Routing_Client(nolock),
Media_Routing_Domain(nolock)
 
WHERE
(Route_Call_Detail.ScriptID = Script.ScriptID)
AND (Route_Call_Detail.MRDomainID = Media_Routing_Domain.MRDomainID)
AND (Script.MasterScriptID = Master_Script.MasterScriptID)
AND (Route_Call_Detail.CallTypeID = Call_Type.CallTypeID)
AND (Route_Call_Detail.RoutingClientID = Routing_Client.RoutingClientID)
AND (Route_Call_Detail.DbDateTime > GetDate()-1) 
--AND (Route_Call_Detail.ANI = '865551234')
--AND (Route_Call_Detail.DialedNumberString = '55555')
--AND (Route_Call_Detail.Label = '55555')
 
ORDER BY 
Route_Call_Detail.DbDateTime DESC

CallType <> DialedNumberString

SELECT     
DialedNumberString = Dialed_Number.DialedNumberString, 
RC_EnterpriseName = Routing_Client.EnterpriseName, 
CT_EnterpriseName = Call_Type.EnterpriseName 
 
FROM         
Routing_Client 
 
INNER JOIN
Dialed_Number ON Routing_Client.RoutingClientID = Dialed_Number.RoutingClientID INNER JOIN
Dialed_Number_Map ON Dialed_Number.DialedNumberID = Dialed_Number_Map.DialedNumberID INNER JOIN
Call_Type ON Dialed_Number_Map.CallTypeID = Call_Type.CallTypeID
 
WHERE
-- Dialed_Number.DialedNumberString = '55555'
Call_Type.EnterpriseName = 'Call_Type_Name'
-- AND Routing_Client.EnterpriseName = 'CVPA1.RC'
 
ORDER BY DialedNumberString

SELECT     
DialedNumberString = Dialed_Number.DialedNumberString, 
RC_EnterpriseName = Routing_Client.EnterpriseName, 
CT_EnterpriseName = Call_Type.EnterpriseName 
 
FROM         
Routing_Client 
 
INNER JOIN
Dialed_Number ON Routing_Client.RoutingClientID = Dialed_Number.RoutingClientID INNER JOIN
Dialed_Number_Map ON Dialed_Number.DialedNumberID = Dialed_Number_Map.DialedNumberID INNER JOIN
Call_Type ON Dialed_Number_Map.CallTypeID = Call_Type.CallTypeID
 
WHERE
Routing_Client.EnterpriseName = 'CVPA1.RC'
 
ORDER BY DialedNumberString

AGENT REAL TIME SUMMARY

SELECT     
Agent_Real_Time.AgentState AS AgentState,
 
CASE Agent_Real_Time.AgentState 
WHEN '0' THEN 'Logged Out'
WHEN '1' THEN 'Logged On'
WHEN '2' THEN 'Not Ready'
WHEN '3' THEN 'Ready'
WHEN '4' THEN 'Talking'
WHEN '5' THEN 'Work Not Ready'
WHEN '6' THEN 'Work Ready'
WHEN '7' THEN 'Busy Other'
WHEN '8' THEN 'Reserved'
WHEN '9' THEN 'Unknown-9'
WHEN '10' THEN 'Calls On Hold'
WHEN '11' THEN 'Active'
WHEN '12' THEN 'Paused'
WHEN '13' THEN 'Interrupted'
WHEN '14' THEN 'Not Active'
 
ELSE 'unknown'
END AS AgentStateName,
COUNT(*) AS COUNT
 
FROM         
Agent_Real_Time INNER JOIN
Agent ON Agent_Real_Time.SkillTargetID = Agent.SkillTargetID INNER JOIN
Person ON Agent.PersonID = Person.PersonID
 
--WHERE
--(Agent_Real_Time.Extension like '7%')
--AND
--(Agent_Real_Time.AgentState = '4')
 
GROUP BY
AgentState

AGENT REAL TIME

SELECT     
Agent_Real_Time.DateTime AS DateTime, 
Agent_Real_Time.AgentState AS AgentState, 
 
CASE Agent_Real_Time.AgentState 
WHEN '0' THEN 'Logged Out'
WHEN '1' THEN 'Logged On'
WHEN '2' THEN 'Not Ready'
WHEN '3' THEN 'Ready'
WHEN '4' THEN 'Talking'
WHEN '5' THEN 'Work Not Ready'
WHEN '6' THEN 'Work Ready'
WHEN '7' THEN 'Busy Other'
WHEN '8' THEN 'Reserved'
WHEN '9' THEN 'Unknown-9'
WHEN '10' THEN 'Calls On Hold'
WHEN '11' THEN 'Active'
WHEN '12' THEN 'Paused'
WHEN '13' THEN 'Interrupted'
WHEN '14' THEN 'Not Active'
 
ELSE 'unknown'
END AS AgentStateName,
 
Person.FirstName AS FirstName, 
Person.LastName AS LastName,
Agent.EnterpriseName AS EnterpriseName,
Agent_Real_Time.Extension AS Extension
 
FROM         
Agent_Real_Time INNER JOIN
Agent ON Agent_Real_Time.SkillTargetID = Agent.SkillTargetID INNER JOIN
Person ON Agent.PersonID = Person.PersonID
 
--WHERE
--(Agent_Real_Time.Extension like '7%')
--AND
--(Agent_Real_Time.AgentState = '4')
 
ORDER BY
 
Person.LastName

Calls in Queue NOW

SELECT     
 
Call_Type.EnterpriseName, 
Call_Type_Real_Time.RouterCallsQNow
 
FROM    
 
Call_Type_Real_Time INNER JOIN
Call_Type ON Call_Type_Real_Time.CallTypeID = Call_Type.CallTypeID
 
ORDER BY 
 
Call_Type_Real_Time.RouterCallsQNow DESC

Calls IN Queue - Skill Group Real Time

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT 
      SGRT.DateTime
      ,SG.EnterpriseName
	  ,SGRT.RouterCallsQNow
      ,SGRT.TalkingIn
      ,SGRT.TalkingOther
      ,SGRT.TalkingOut
      ,SGRT.CallsQueuedNow
      ,SGRT.CallsInProgress
      ,SGRT.WorkReady
      ,SGRT.WorkNotReady
      ,SGRT.LongestCallQ
      ,SGRT.LoggedOn
      ,SGRT.NotReady
      ,SGRT.Ready
      ,SGRT.LongestAvailAgent
	  ,SGRT.SkillTargetID
      ,SGRT.AgentOutCallsTo5
      ,SGRT.AnswerWaitTimeTo5
      ,SGRT.Avail
      ,SGRT.AvailTimeTo5
      ,SGRT.AvgHandledCallsTalkTimeTo5
      ,SGRT.AvgHandledCallsTimeTo5
      ,SGRT.BusyOther
      ,SGRT.BusyOtherTimeTo5
      ,SGRT.CallsAnsweredTo5
      ,SGRT.CallsHandledTo5
      ,SGRT.CallsOfferedTo5
      ,SGRT.HandledCallsTalkTimeTo5
      ,SGRT.HandledCallsTimeTo5
      ,SGRT.AgentOutCallsTimeTo5
      ,SGRT.Hold
      ,SGRT.HoldTimeTo5
      ,SGRT.LoggedOnTimeTo5
      ,SGRT.NotReadyTimeTo5
      ,SGRT.PercentUtilizationTo5
      ,SGRT.ReservedAgents
      ,SGRT.ReservedStateTimeTo5
      ,SGRT.TalkInTimeTo5
      ,SGRT.TalkOtherTimeTo5
      ,SGRT.TalkOutTimeTo5
      ,SGRT.TalkTimeTo5
      ,SGRT.TransferInCallsTimeTo5
      ,SGRT.TransferInCallsTo5
      ,SGRT.TransferOutCallsTo5
      ,SGRT.WorkNotReadyTimeTo5
      ,SGRT.WorkReadyTimeTo5
      ,SGRT.AutoOutCallsTo5
      ,SGRT.AutoOutCallsTimeTo5
      ,SGRT.AutoOutCallsTalkTimeTo5
      ,SGRT.PreviewCallsTo5
      ,SGRT.PreviewCallsTimeTo5
      ,SGRT.PreviewCallsTalkTimeTo5
      ,SGRT.ReserveCallsTo5
      ,SGRT.ReserveCallsTimeTo5
      ,SGRT.ReserveCallsTalkTimeTo5
      ,SGRT.TalkingAutoOut
      ,SGRT.TalkingPreview
      ,SGRT.TalkingReserve
      ,SGRT.TalkAutoOutTimeTo5
      ,SGRT.TalkPreviewTimeTo5
      ,SGRT.TalkReserveTimeTo5
      ,SGRT.NumAgentsInterruptedNow
      ,SGRT.InterruptedTimeTo5
      ,SGRT.RouterLongestCallInQ
      ,SGRT.IcmAvailable
      ,SGRT.ApplicationAvailable
      ,SGRT.RedirectNoAnsCallsTo5
      ,SGRT.RouterCallsOfferedTo5
      ,SGRT.RouterCallsAbandToAgentTo5
      ,SGRT.RouterCallsDequeuedTo5
      ,SGRT.ServiceLevelTo5
      ,SGRT.ServiceLevelCallsTo5
      ,SGRT.ServiceLevelCallsAbandTo5
      ,SGRT.ServiceLevelCallsDequeuedTo5
      ,SGRT.ServiceLevelRONATo5
      ,SGRT.ServiceLevelCallsOfferedTo5
      ,SGRT.RouterCallsAbandQTo5
      ,SGRT.FutureUseInt1
      ,SGRT.FutureUseInt2
      ,SGRT.FutureUseInt3
      ,SGRT.FutureUseInt4
      ,SGRT.FutureUseInt5
      ,SGRT.RouterCallsAbandDequeuedTo5
  FROM 
  vhi_awdb.dbo.Skill_Group_Real_Time SGRT,
  vhi_awdb.dbo.Skill_Group SG
 
  WHERE 
 SGRT.SkillTargetID = SG.SkillTargetID
 
ORDER BY SGRT.RouterCallsQNow DESC, SG.EnterpriseName ASC

SQL Query for TCD and joining the Call Detail Table

(Thanks to Jason Williams)

SELECT tcd.DateTime,tcd.DigitsDialed,rcd.Label,tcd.PeripheralID,*
 
FROM Termination_Call_Detail AS tcd, Route_Call_Detail AS rcd 
 
WHERE 
 
CallDisposition = 1 AND 
tcd.DateTime > GetDate()-1 AND 
rcd.DateTime > GetDate()-1 AND 
rcd.RouterCallKey = tcd.RouterCallKey AND 
rcd.RouterCallKeyDay = tcd.RouterCallKeyDay

SELECT tcd.DateTime,tcd.DigitsDialed,rcd.Label,tcd.PeripheralNumber,* 
FROM
Termination_Call_Detail AS tcd, Route_Call_Detail AS rcd 
WHERE 
CallDisposition = 1 AND 
tcd.DateTime > '22 June 2010 00:00' AND 
rcd.DateTime > '22 June 2010 00:00' AND 
rcd.RouterCallKey = tcd.RouterCallKey AND 
rcd.RouterCallKeyDay = tcd.RouterCallKeyDay

IPIVR SQL queries

SELECT Top 10 DateTime,MaxCallsInProgress,* 
FROM Peripheral_Half_Hour 
WHERE PeripheralID IN('5001', '5002') 
ORDER BY MaxCallsInProgress DESC

Total Calls Handled in a Half Hour

SELECT SUM(CallsOffered)AS Calls ,DateTime 
FROM Peripheral_Interval
WHERE DateTime > GetDate()-1
GROUP BY DateTime
ORDER BY Calls DESC

SELECT SUM(CallsHandled) AS TotalHandledCalls,DateTime  
FROM Call_Type_Interval
WHERE DateTime > GetDate()-1
GROUP BY DateTime
ORDER BY TotalHandledCalls DESC

Number of Concurrent Agent Logged from Agent Half Hour Table

Note: there will be some overlap, as this will document the total agent logged in, in a half hour period. So if there is a high Agent “Swap count” due to change in shift during the half hour, concurrent agent count will be lower than the figure determined here
Remove the Where DateTime Line to get max number of agents logged in over a half hour over life of HDS data.

SELECT DateTime,COUNT(*)AS AgentCount 
FROM Agent_Interval
WHERE DateTime > Getdate()-1
AND MRDomainID = '1'
GROUP BY DateTime
ORDER BY AgentCount DESC

SQL Join and Case Example

SELECT     
Agent.EnterpriseName, 
Agent_Event_Detail.DateTime, 
Agent_Event_Detail.Event , 
 
CASE Agent_Event_Detail.Event  
WHEN '1' THEN 'Login'
WHEN '2' THEN 'Logout'
WHEN '3' THEN 'Not Ready'
ELSE 'unknown'
END AS Event_Transition,
 
Agent_Event_Detail.LoginDateTime, 
Agent_Event_Detail.RecoveryKey, 
Agent_Event_Detail.Duration, 
Reason_Code.ReasonText
 
FROM         
Reason_Code 
 
INNER JOIN
Agent_Event_Detail ON Reason_Code.ReasonCode = Agent_Event_Detail.ReasonCode INNER JOIN
Agent ON Agent_Event_Detail.SkillTargetID = Agent.SkillTargetID

Number of Named / Distinct Agents over the last X Days (in this example 30)

SELECT COUNT(DISTINCT SkillTargetID) 
  FROM [aib_awdb].[dbo].[Agent_Logout]
  WHERE LogoutDateTime > GetDate()-30

Details (Names, Team) of the Named Agents over the last X Days

SELECT 
DISTINCT 
 SkillTargetID = AL.SkillTargetID
,AgentEnterpriseName = A.EnterpriseName
,FirstName = P.FirstName
,LastName = P.LastName 
,LoginName = P.LoginName
,AgentTeam = AT.EnterpriseName 
 
FROM [ucce_awdb].[dbo].[Agent_Logout] AL
LEFT JOIN Agent A ON A.SkillTargetID = AL.SkillTargetID
LEFT JOIN Agent_Team_Member ATM ON A.SkillTargetID = ATM.SkillTargetID
LEFT JOIN Agent_Team AT ON ATM.AgentTeamID = AT.AgentTeamID
LEFT JOIN Person P ON A.PersonID = P.PersonID
WHERE AL.LogoutDateTime > GetDate()-45
ORDER BY AT.EnterpriseName

Configured Agents

SELECT COUNT (DISTINCT SkillTargetID)
  FROM [aib_awdb].[dbo].[Agent]
  WHERE Deleted = 'N'

Agent Real Time State with Teams

SELECT     
Agent_Team.EnterpriseName AS AgentTeam,
Person.FirstName, 
Person.LastName, 
Person.LoginName, 
Agent_Real_Time.AgentState, 
CASE Agent_Real_Time.AgentState 
WHEN '0' THEN 'Logged Out'
WHEN '1' THEN 'Logged On'
WHEN '2' THEN 'Not Ready'
WHEN '3' THEN 'Ready'
WHEN '4' THEN 'Talking'
WHEN '5' THEN 'Work Not Ready'
WHEN '6' THEN 'Work Ready'
WHEN '7' THEN 'Busy Other'
WHEN '8' THEN 'Reserved'
WHEN '9' THEN 'Unknown-9'
WHEN '10' THEN 'Calls On Hold'
WHEN '11' THEN 'Active'
WHEN '12' THEN 'Paused'
WHEN '13' THEN 'Interrupted'
WHEN '14' THEN 'Not Active'
ELSE 'unknown'
END AS AgentStateName,
Agent_Real_Time.Extension
 
FROM         
Agent INNER JOIN
Person ON Agent.PersonID = Person.PersonID INNER JOIN
Agent_Team_Member ON Agent.SkillTargetID = Agent_Team_Member.SkillTargetID INNER JOIN
Agent_Team ON Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID INNER JOIN
Agent_Real_Time ON Agent.SkillTargetID = Agent_Real_Time.SkillTargetID
 
--WHERE
--(Agent_Real_Time.Extension like '7%')
--AND
--(Agent_Real_Time.AgentState = '4')
 
 
ORDER BY 
Agent_Team.EnterpriseName,
FirstName,
LastName

To view the different numbers for particular CallTypes

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
 
SELECT DialedNumberString, COUNT(*) AS CallCount
FROM 
Route_Call_Detail (nolock)
WHERE 
(CallTypeID = '5001' OR CallTypeID = '5002') AND
(Route_Call_Detail.DbDateTime >= Getdate()-1)
 
GROUP BY DialedNumberString
 
ORDER BY DialedNumberString

Call Count for Calls which terminate with Calltype and dialed number

SELECT COUNT(*) AS CallCount
FROM 
Route_Call_Detail (nolock)
WHERE 
(DialedNumberString) = '|12345' AND
(CallTypeID = '5001') AND
(Route_Call_Detail.DbDateTime >= '07/29/2013 00:00:00') AND
(Route_Call_Detail.DbDateTime <= '07/30/2013 00:00:00')

How to get the time for one hour ago.

SELECT DateAdd(hh,-1,GetDate())
 
SELECT * FROM <tablename> WHERE DateTime > DateAdd(hh,-1,GetDate())

Get a Count by DialedNumberString in the last Hour

SELECT DialedNumberString, COUNT(DialedNumberString) AS NumberOfCalls
FROM Route_Call_Detail
WHERE DateTime > (DateAdd(hh,-1,GetDate()))
GROUP BY DialedNumberString 
ORDER BY DialedNumberString

Select Extension number NOT used

DECLARE @StartExt INT = 10000;
DECLARE @EndExt INT = 10250;
 
 
SELECT (@StartExt + NUMBER) AS Extension 
FROM master..spt_values
WHERE TYPE = 'P' 
AND NUMBER BETWEEN 0 AND (@EndExt - @StartExt)
AND (@StartExt + NUMBER) NOT IN
(SELECT <dbname>..Agent_Real_Time.Extension FROM <dbname>..Agent_Real_Time WHERE Extension BETWEEN @StartExt AND @EndExt)

SELECT Datepart (HOUR, DateTime) AS HOUR, COUNT(*) AS CALLS
  FROM [ipcc1_awdb].[dbo].[Termination_Call_Detail]
  WHERE 
  DateTime > '2014-02-03 00:00'
AND DateTime < '2014-02-03 23:59'
 
 GROUP BY 
Datepart (HOUR, DateTime)

Top Call by Dialed Number String

SELECT 
DialedNumberString, COUNT(*) AS COUNT
 
FROM
 
dbo.Route_Call_Detail
WHERE DateTime > GetDate()-1
 
GROUP BY DialedNumberString
 
ORDER BY COUNT DESC

Campaigns <> Query Rules

USE ipcc1_awdb
 
SELECT     
Campaign_Query_Rule.CampaignID, 
Query_Rule.QueryRuleID, 
Campaign.CampaignName, 
Campaign.Enabled AS CampaignEnabled, 
Query_Rule.QueryRuleName, 
Campaign_Query_Rule.StartHours, 
Campaign_Query_Rule.ListOrder, 
Campaign_Query_Rule.StartMinutes, 
Campaign_Query_Rule.EndHours, 
Campaign_Query_Rule.EndMinutes, 
Campaign_Query_Rule.Penetration, 
Campaign_Query_Rule.Duration, 
Campaign_Query_Rule.HitRate
 
FROM         
Campaign_Query_Rule 
INNER JOIN
Campaign ON Campaign_Query_Rule.CampaignID = Campaign.CampaignID INNER JOIN
Query_Rule ON Campaign_Query_Rule.QueryRuleID = Query_Rule.QueryRuleID
 
WHERE Campaign.Enabled = 'Y'
ORDER BY CampaignID, QueryRuleID

Campaign SELECT

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [DialingListID]
      ,[Phone01]
      ,[CallbackNumber]
      ,[CallResult]
      ,[CallResult01]
      ,[CallResult02]
      ,[LastZoneDialed]
      ,[LastNumberDialedZone1]
      ,[LastNumberDialedZone2]
      ,[CallsMadeToZone1]
      ,[CallsMadeToZone2]
      ,[CallbackDateTimeZone1]
      ,[CallbackDateTime01]
      ,[CallbackDateTime02]
      ,[GMTPhone01]
      ,[GMTPhone02]
      ,[DSTPhone01]
      ,[DSTPhone02]
      ,[CallStatusZone1]
      ,[CallStatusZone2]
      ,[AccountNumber]
      ,[LastName]
      ,[FirstName]
      ,[ImportRuleDate]
  FROM [ipcc1_baA].[dbo].[DL_XXXX_YYYY]
  WHERE 
  ((CallStatusZone1 = 'A')
  OR(CallStatusZone1 = 'P'))
  AND ImportRuleDate < '2014-09-14'
  ORDER BY DialingListID DESC

Campaign Close Records

/****** Script for SelectTopNRows command from SSMS  ******/
UPDATE [DL_5023_5026]
SET CallStatusZone1='C',
CallStatusZone2='C'
 
  WHERE 
  ((CallStatusZone1 = 'A')
  OR(CallStatusZone1 = 'P'))
  AND ImportRuleDate < '2014-09-14'

Realtime SQL for Campaign Contacts - should only be used for Troubleshooting

http://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/outbound_option/outboundoption9_0/installation/guide/UCCE_BK_O4A87BBC_00_outbound-option-guide-for-cisco/UCCE_BK_O4A87BBC_00_outbound-option-guide-for-cisco_appendix_01100.html

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT 'CampaignTitleName' AS CampaignTitle
	  ,[DialingListID]
      ,[Phone01]
      ,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'
 
      ,[CallsMadeToZone1] AS CallAttempts
      ,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'
      ,[CallbackNumber]
      ,[CallbackDateTime01] AS CallbackDateTime
      ,[AccountNumber]
      ,[LastName]
      ,[FirstName]
      ,[ImportRuleDate]
  FROM [<icm-instance-name>_baA].[dbo].[DL_CCCC_QQQQ]
  WHERE 
  ImportRuleDate > GETDATE()-100
  ORDER BY DialingListID DESC

Dialer_Detail SQL Query - HDS Database

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
--USE ipcc1_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,
Agent.EnterpriseName,
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,
Campaign,
Query_Rule, 
Import_Rule,
Skill_Group,
Agent
 
WHERE 
Dialer_Detail.DateTime > GETDATE()-10
 
AND Dialer_Detail.CampaignID = Campaign.CampaignID
AND Dialer_Detail.QueryRuleID = Query_Rule.QueryRuleID
AND Query_Rule.ImportRuleID = Import_Rule.ImportRuleID
AND Skill_Group.SkillTargetID = Dialer_Detail.SkillGroupSkillTargetID
AND (Dialer_Detail.PeripheralID = Agent.PeripheralID AND Dialer_Detail.AgentPeripheralNumber = Agent.PeripheralNumber)
 
--AND Dialer_Detail.Phone like '08%'
--AND Len(Dialer_Detail.Phone) != '10'
 
ORDER BY DateTime DESC

TO GET THE CAMPAIGN AND QUERY RULE ID

USE <icm-instance-name>_awdb
 
SELECT     
Campaign_Query_Rule.CampaignID, 
Query_Rule.QueryRuleID, 
Campaign.CampaignName, 
Campaign.Enabled AS CampaignEnabled, 
Campaign.[MaxAttempts],
Query_Rule.QueryRuleName, 
Campaign_Query_Rule.ListOrder, 
Campaign_Query_Rule.StartHours, 
Campaign_Query_Rule.StartMinutes, 
Campaign_Query_Rule.EndHours, 
Campaign_Query_Rule.EndMinutes,
Campaign.[HomeEnabled] AS CampaignZone1Enabled,
Campaign.[HomeStartHours] AS CampaignZone1StartHour,
Campaign.[HomeStartMinutes] AS CampaignZone1StartMinutes,
Campaign.[HomeEndHours] AS CampaignZone1EndHour,
Campaign.[HomeEndMinutes] AS CampaignZone1EndMinutes
 
FROM         
Campaign_Query_Rule 
INNER JOIN
Campaign ON Campaign_Query_Rule.CampaignID = Campaign.CampaignID INNER JOIN
Query_Rule ON Campaign_Query_Rule.QueryRuleID = Query_Rule.QueryRuleID
 
WHERE Campaign.Enabled = 'Y'
ORDER BY CampaignID, QueryRuleID

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
USE ucce_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,
--Agent.EnterpriseName,
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,
Campaign,
Query_Rule, 
Import_Rule,
Skill_Group
--Agent
 
WHERE 
Dialer_Detail.DateTime > GETDATE()-1
 
AND Dialer_Detail.CampaignID = Campaign.CampaignID
AND Dialer_Detail.QueryRuleID = Query_Rule.QueryRuleID
AND Query_Rule.ImportRuleID = Import_Rule.ImportRuleID
 
AND Skill_Group.SkillTargetID = Dialer_Detail.SkillGroupSkillTargetID
--AND ((Dialer_Detail.AgentPeripheralNumber = Agent.PeripheralNumber) OR (Dialer_Detail.AgentPeripheralNumber = NULL))
AND (Dialer_Detail.AgentPeripheralNumber IS NULL)
 
ORDER BY DateTime DESC

Agent Wrapup Daily

/****** Script for SelectTopNRows command from SSMS  ******/
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
 
;WITH AgentWrapupDaily (PeripheralID,AgentSkillTargetID,DateTime,WrapupData,WorkTime)
AS
(SELECT 
	  [PeripheralID]
      ,[AgentSkillTargetID]
      ,CONVERT(CHAR(10),[DateTime],101) 
      ,[WrapupData]
      ,[WorkTime]
 
  FROM [Termination_Call_Detail] nolock
  WHERE
  AgentSkillTargetID IS NOT NULL)
 
  SELECT 
	   [PeripheralID]
      ,[AgentSkillTargetID]
	  ,CONVERT(DateTime,[DateTime],101) AS DateTime
      ,isNULL([WrapupData],'') AS WrapupData
      ,isNULL(SUM(WorkTime),0) AS WorkTimeSum
  FROM AgentWrapupDaily
  GROUP BY PeripheralID, AgentSkillTargetID, DateTime, WrapupData
  ORDER BY AgentSkillTargetID, DateTime DESC

Agent Peripheral ID from Extension

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
 
SELECT Agent.PeripheralNumber
FROM 
Agent,
AgentRealTime
 
WHERE
Agent.SkillTargetID = Agent_Real_Time.SkillTargetID AND
Agent_Real_Time.Extension = '<extension>'

SIZING

Max Logged in Agents

SELECT [DateTime]
       ,[MaxNumberLoggedOnAgents]
 
       FROM [vhi_awdb].[dbo].[Peripheral_Interval]
       WHERE DateTime > GetDate()-365
       ORDER BY MaxNumberLoggedOnAgents DESC

To find Average Skills per agent - find out how many configured agents and then how many agents assigned to skills (below).

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT SGM.[SkillGroupSkillTargetID]
      ,SGM.[AgentSkillTargetID]
	  ,A.EnterpriseName
	  ,A.Deleted
  FROM 
  [vhi_awdb].[dbo].[Skill_Group_Member] SGM
 
  LEFT JOIN Agent A ON SGM.AgentSkillTargetID = A.SkillTargetID
  WHERE A.Deleted = 'N'

Max Queued Calls for a specific Interval

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT [DateTime]
     -- ,[CallTypeID]
	  ,CallTypeName = CT.[EnterpriseName]
      ,[MaxCallsQueued]
      ,[TimeZone]
      ,[RecoveryKey]
      ,[RouterQueueWaitTime]
      ,[RouterQueueCalls]
      ,[AvgRouterDelayQ]
      ,[RouterCallsAbandQ]
      ,[RouterQueueCallTypeLimit]
      ,[RouterQueueGlobalLimit]
      ,[CallsRouted]
 
      ,[MaxCallWaitTime]
      ,[ReservationCalls]
  FROM [vhi_awdb].[dbo].[Call_Type_Interval] CTI
  --Where DateTime > GetDate()-365
  LEFT JOIN Call_Type CT ON CT.CallTypeID=CTI.CallTypeID
  WHERE CTI.DateTime = '2019-08-12 12:30:00'
  AND CTI.MaxCallsQueued > 0
  ORDER BY MaxCallsQueued ASC

Busiest Interval by CallType

SELECT TOP (5)    
Call_Type_Interval.DateTime, 
Call_Type_Interval.CallTypeID, 
Call_Type.EnterpriseName, 
Call_Type_Interval.CallsOffered
 
FROM         
Call_Type_Interval INNER JOIN
Call_Type ON Call_Type_Interval.CallTypeID = Call_Type.CallTypeID
 
WHERE DateTime > GetDate()-1
ORDER BY CallsOffered DESC

Peak Call Offered for a specific 30 min interval

SELECT COUNT(*) AS CallCount
FROM 
Route_Call_Detail (nolock)
WHERE 
(Route_Call_Detail.DbDateTime >= '03/10/2015 11:00:00') AND
(Route_Call_Detail.DbDateTime <= '03/10/2015 11:30:00')

Average Handle Time

SELECT  TOP (5)   
Skill_Group_Interval.DateTime, 
Skill_Group_Interval.SkillTargetID, 
Skill_Group.EnterpriseName, 
Skill_Group_Interval.CallsHandled, 
Skill_Group_Interval.AvgHandledCallsTime
 
FROM         
Skill_Group_Interval INNER JOIN
Skill_Group ON Skill_Group_Interval.SkillTargetID = Skill_Group.SkillTargetID
WHERE DateTime > '2016-01-04 00:00:00'
AND DateTime < '2016-01-05 00:00:00'
 
ORDER BY CallsHandled DESC

Average Call Values for a Agent Handled Calls for an specific interval period

DECLARE @start_date Datetime
DECLARE @end_date Datetime
--SET @start_date = GETDate()-1
--SET @end_date = GetDate()
SET @start_date = '2016-01-04 11:00:00'
SET @end_date = '2016-01-04 12:00:00'
 
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
 
SELECT --TOP (100)
Duration = AVG(Termination_Call_Detail.Duration)
,NetworkTime = AVG(Termination_Call_Detail.NetworkTime)
,NetQTime = AVG(Termination_Call_Detail.NetQTime)
,LocalQTime = AVG(Termination_Call_Detail.LocalQTime)
,RingTime = AVG(Termination_Call_Detail.RingTime)
,DelayTime = AVG(Termination_Call_Detail.DelayTime)
,TimeToAband = AVG(Termination_Call_Detail.TimeToAband)
,TalkTime = AVG(Termination_Call_Detail.TalkTime)
,HoldTime = AVG(Termination_Call_Detail.HoldTime)
,CallDuration = AVG(Termination_Call_Detail.TalkTime+Termination_Call_Detail.HoldTime)
,ConferenceTime = AVG(Termination_Call_Detail.ConferenceTime)
,WorkTime = AVG(Termination_Call_Detail.WorkTime)
 
 
  FROM 
  Termination_Call_Detail (nolock)
  LEFT JOIN Media_Routing_Domain (nolock) ON (Termination_Call_Detail.MRDomainID = Media_Routing_Domain.MRDomainID)
  LEFT JOIN Agent (nolock) ON (Termination_Call_Detail.AgentSkillTargetID = Agent.SkillTargetID)
  LEFT JOIN Agent SAgent (nolock) ON (Termination_Call_Detail.SourceAgentSkillTargetID = SAgent.SkillTargetID)
  LEFT JOIN Person (nolock) ON (Agent.PersonID = Person.PersonID)
  LEFT JOIN Person SPerson (nolock) ON (SAgent.PersonID = SPerson.PersonID)
  LEFT JOIN Agent_Team_Member (nolock) ON (Agent.SkillTargetID = Agent_Team_Member.SkillTargetID)
  LEFT JOIN Agent_Team (nolock) ON (Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID)
  LEFT JOIN Skill_Group (nolock) ON (Termination_Call_Detail.SkillGroupSkillTargetID = Skill_Group.SkillTargetID)
--  LEFT JOIN Precision_Queue (nolock) on (Termination_Call_Detail.PrecisionQueueID = Precision_Queue.PrecisionQueueID)
  LEFT JOIN Call_Type (nolock) ON (Termination_Call_Detail.CallTypeID = Call_Type.CallTypeID)
 
 WHERE 
 
  Termination_Call_Detail.DateTime >=  @start_date
  AND Termination_Call_Detail.DateTime < @end_date
  --Termination_Call_Detail.PeripheralID = '5000'
  AND RouterCallKey <> '0'
  AND Termination_Call_Detail.PeripheralCallType = '2'

Active Teams

Look at the distinct agents who have logged in over the last 24 hours - and sort by teams. In Excel - you can gran Teams and remove duplicates to get a clean list of the exact teams therefore used over 24 hours.

SELECT     
DISTINCT AL.SkillTargetID,
A.PeripheralID,
A.PeripheralNumber,
A.PersonID, 
P.FirstName, 
P.LastName, 
P.LoginName,
T.EnterpriseName AS AgentTeamName
 
FROM Agent_Logout AL
LEFT JOIN Agent A ON A.SkillTargetID = AL.SkillTargetID
LEFT JOIN Agent_Team_Member ATM ON A.SkillTargetID = ATM.SkillTargetID
LEFT JOIN Agent_Team T ON ATM.AgentTeamID = T.AgentTeamID
LEFT JOIN Person P ON A.PersonID = P.PersonID
 
WHERE AL.DbDateTime > GetDate()-1
ORDER BY T.EnterpriseName,FirstName,LastName

Troubleshooting

On Logger Only by default!

SELECT MessageId, CentralControllerFileTime, Side,SourceSystemName,Category,ProcName,Severity,MessageString FROM t_Event
WHERE Severity IN ('Warning','Error','Informational')
ORDER BY CentralControllerFileTime DESC

Repeat Callers

Repeat Callers by Calltype - Summary

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
;WITH TEMP1 AS
(
SELECT 
CallTypeID = RCD.CallTypeID
,ANI = RCD.ANI
,IndivualCustCalls = COUNT(*)
 
FROM [Route_Call_Detail] RCD (nolock)
 
WHERE 
DateTime >= :start_date
AND DateTime < :end_date
AND ANI LIKE '+353%' /* remove anonymous and agent calls, by only include Valid Irish CLI Calls */ 
--AND ANI NOT LIKE '%00' /* remove ANI ending in 00 */
AND RoutingClientID IN (5000,5001,5002) /*include only Voice Routing Clients */
AND RouterErrorCode = 0 /*Successful Calls */
AND Label IS NOT NULL /*Connected to Agents - and not a Release node etc. */
 
GROUP BY CallTypeID, ANI
)
 
 
,TEMP2 AS
(
SELECT 
 CallTypeID = CallTypeID 
,IndivualCustCallCount = IndivualCustCalls
,IndivualCustCallRange = CASE 
WHEN IndivualCustCalls = 1 THEN '1'
WHEN IndivualCustCalls = 2 THEN '2'
WHEN IndivualCustCalls = 3 THEN '3'
WHEN IndivualCustCalls = 4 THEN '4'
WHEN IndivualCustCalls = 5 THEN '5'
WHEN IndivualCustCalls BETWEEN 6 AND 10 THEN '6-10'
WHEN IndivualCustCalls BETWEEN 11 AND 20 THEN '11-20'
WHEN IndivualCustCalls BETWEEN 21 AND 30 THEN '21-30'
WHEN IndivualCustCalls BETWEEN 31 AND 40 THEN '31-40'
WHEN IndivualCustCalls BETWEEN 41 AND 50 THEN '41-50'
WHEN IndivualCustCalls > 50 THEN '>50'
ELSE 'ERROR'
END
,COUNT = COUNT(*)
 
FROM TEMP1
GROUP BY CallTypeID, IndivualCustCalls)
 
,TEMP3 AS
(
SELECT 
CallTypeID = CallTypeID 
,IndivualCustCallRange 
,COUNT = SUM(COUNT)
FROM TEMP2
GROUP BY CallTypeID,IndivualCustCallRange
)
 
SELECT 
 CallTypeID = PV.CallTypeID
,CallTypeName = CT.EnterpriseName
,[1] = isNull (PV.[1],0)
,[1_percent] = ((isNull (PV.[1],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,[2] = isNUll (PV.[2],0)
,[2_percent] = ((isNull (PV.[2],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,[3] = isNUll (PV.[3],0)
,[3_percent] = ((isNull (PV.[3],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,[4] = isNUll (PV.[4],0)
,[4_percent] = ((isNull (PV.[4],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,[5] = isNUll (PV.[5],0)
,[5_percent] = ((isNull (PV.[5],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,[6-10] = isNUll (PV.[6-10],0)
,[6-10_percent] = ((isNull (PV.[6-10],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,[11-20] = isNUll (PV.[11-20],0)
,[11-20_percent] = ((isNull (PV.[11-20],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,[21-30] = isNUll (PV.[21-30],0)
,[21-30_percent] = ((isNull (PV.[21-30],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,[31-40] = isNUll (PV.[31-40],0)
,[31-40_percent] = ((isNull (PV.[31-40],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,[41-50] = isNUll (PV.[41-50],0)
,[41-50_percent] = ((isNull (PV.[41-50],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,[>50] = isNUll (PV.[>50],0)
,[>50_percent] = ((isNull (PV.[>50],0)) * 1.0) / ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
,TOTAL = ((isNull (PV.[1],0)) + (isNUll (PV.[2],0)) + (isNUll (PV.[3],0)) + (isNUll (PV.[4],0)) + (isNUll (PV.[5],0)) + (isNUll (PV.[6-10],0)) + (isNUll (PV.[11-20],0)) + (isNUll (PV.[21-30],0)) + (isNull (PV.[31-40],0)) + (isNull (PV.[41-50],0)) + (isNull (PV.[>50],0)))
 
FROM TEMP3
 
PIVOT (MAX(COUNT) FOR IndivualCustCallRange IN ([1],[2],[3],[4],[5],[6-10],[11-20],[21-30],[31-40],[41-50],[>50])) AS PV
LEFT JOIN Call_Type CT ON CT.CallTypeID = PV.CallTypeID
 
ORDER BY [1] DESC, CallTypeName
<code>
 
==== Repeat Callers BY Calltype - Detail ====
<code sql>
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT TOP (8001)
 CallTypeID = T1.CallTypeID
,CallTypeName = CT.EnterpriseName
,ANI = T1.ANI
,IndivualCustCalls = T1.IndivualCustCalls
 
FROM 
(
SELECT TOP (8002)
 CallTypeID = RCD.CallTypeID
,ANI = RCD.ANI
,IndivualCustCalls = COUNT(*)
 
FROM [Route_Call_Detail] RCD (nolock)
 
WHERE 
 
RCD.DateTime >= :start_date
AND RCD.DateTime < :end_date
AND RoutingClientID IN (5000,5001,5002) /*include only Voice Routing Clients */
AND RouterErrorCode = 0 /*Successful Calls */
AND Label IS NOT NULL /*Connected to Agents - and not a Release node etc. */
AND ANI LIKE '+353%' /* remove anonymous and agent calls */
--AND ANI NOT LIKE '%00' /* remove ANI ending in 00 */
 
GROUP BY CallTypeID, ANI
HAVING COUNT(*) > 1
) T1
LEFT JOIN Call_Type CT ON CT.CallTypeID = T1.CallTypeID
WHERE IndivualCustCalls > 1
ORDER BY IndivualCustCalls DESC

Repeat Callers by Calltype - Detailed

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT 
 CallTypeID = T1.CallTypeID
,CallTypeName = CT.EnterpriseName
,ANI = T1.ANI
,IndivualCustCalls = T1.IndivualCustCalls
 
FROM 
(
SELECT TOP (8001)
 CallTypeID = RCD.CallTypeID
,ANI = RCD.ANI
,IndivualCustCalls = COUNT(*)
 
FROM [Route_Call_Detail] RCD (nolock)
 
WHERE 
 
RCD.DateTime >= :start_date
AND RCD.DateTime < :end_date
AND RoutingClientID IN (5000,5001,5002) /*include only Voice Routing Clients */
AND RouterErrorCode = 0 /*Successful Calls */
AND Label IS NOT NULL /*Connected to Agents - and not a Release node etc. */
AND ANI LIKE '+353%' /* remove anonymous and agent calls */
--AND ANI NOT LIKE '%00' /* remove ANI ending in 00 */
 
GROUP BY CallTypeID, ANI
HAVING COUNT(*) > 1
) T1
LEFT JOIN Call_Type CT ON CT.CallTypeID = T1.CallTypeID
WHERE IndivualCustCalls > 1
ORDER BY IndivualCustCalls DESC

Grouping RCD Table by ANI

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
;WITH TEMP1 AS
(SELECT 
--[ANI],
NumberType = CASE 
WHEN ANI LIKE '08%' THEN 'Irish Mobile'
WHEN ANI LIKE '+3538%' THEN 'Irish Mobile'
WHEN ANI LIKE '+353%' THEN 'Irish Landline'
WHEN (ANI LIKE '+8%' AND LEN(ANI) = 10) THEN 'Irish Mobile'
WHEN ANI LIKE '+447%' THEN 'UK Mobile'
WHEN ANI LIKE '+00447%' THEN 'UK Mobile'
WHEN ANI LIKE '07%' THEN 'UK Mobile'
WHEN ANI LIKE '+44%' THEN 'UK Landline'
WHEN ANI LIKE '+0044%' THEN 'UK Landline'
WHEN ANI LIKE '+001%' THEN 'US or Canada'
WHEN ANI LIKE '+1%' THEN 'US or Canada'
WHEN ANI LIKE '+0033%' THEN 'France'
WHEN ANI LIKE '+33%' THEN 'France'
WHEN ANI LIKE '+0031%' THEN 'Netherlands'
WHEN ANI LIKE '+31%' THEN 'Netherlands'
WHEN ANI LIKE '+0034%' THEN 'Spain'
WHEN ANI LIKE '+34%' THEN 'Spain'
WHEN ANI LIKE '+00351%' THEN 'Portugal'
WHEN ANI LIKE '+351%' THEN 'Portugal'
WHEN ANI LIKE '+0061%' THEN 'Australia'
WHEN ANI LIKE '+61%' THEN 'Australia'
WHEN ANI LIKE '+83%' THEN 'North Korea'
 
ELSE 'OTHER'
END
 
FROM [aib_awdb].[dbo].[Route_Call_Detail]
WHERE DateTime > GetDate()-7
AND ANI <> 'NULL' 
AND ANI <> '+'
AND LEN(ANI) > 7 
AND ANI <> 'unavailable'
AND ANI <> 'anonymous'
)
 
SELECT 
NumberType,
COUNT = COUNT(*)
FROM TEMP1
 
GROUP BY NumberType
ORDER BY COUNT DESC

Finding the other numbers

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
;WITH TEMP1 AS
(SELECT 
[ANI],
NumberType = CASE 
WHEN ANI LIKE '08%' THEN 'Irish Mobile'
WHEN ANI LIKE '+3538%' THEN 'Irish Mobile'
WHEN ANI LIKE '+353%' THEN 'Irish Landline'
WHEN (ANI LIKE '+8%' AND LEN(ANI) = 10) THEN 'Irish Mobile'
WHEN ANI LIKE '+447%' THEN 'UK Mobile'
WHEN ANI LIKE '+00447%' THEN 'UK Mobile'
WHEN ANI LIKE '07%' THEN 'UK Mobile'
WHEN ANI LIKE '+44%' THEN 'UK Landline'
WHEN ANI LIKE '+0044%' THEN 'UK Landline'
WHEN ANI LIKE '+001%' THEN 'US or Canada'
WHEN ANI LIKE '+1%' THEN 'US or Canada'
WHEN ANI LIKE '+0033%' THEN 'France'
WHEN ANI LIKE '+33%' THEN 'France'
WHEN ANI LIKE '+0031%' THEN 'Netherlands'
WHEN ANI LIKE '+31%' THEN 'Netherlands'
WHEN ANI LIKE '+0034%' THEN 'Spain'
WHEN ANI LIKE '+34%' THEN 'Spain'
WHEN ANI LIKE '+00351%' THEN 'Portugal'
WHEN ANI LIKE '+351%' THEN 'Portugal'
WHEN ANI LIKE '+0061%' THEN 'Australia'
WHEN ANI LIKE '+61%' THEN 'Australia'
WHEN ANI LIKE '+83%' THEN 'North Korea'
 
ELSE 'OTHER'
END
 
FROM [aib_awdb].[dbo].[Route_Call_Detail]
WHERE DateTime > GetDate()-7
AND ANI <> 'NULL' 
AND ANI <> '+'
AND LEN(ANI) > 7 
AND ANI <> 'unavailable'
AND ANI <> 'anonymous'
 
)
 
SELECT 
ANI,
NumberType
FROM TEMP1
WHERE NumberType='OTHER'
ORDER BY NumberType,ANI

Table of Contents