Dieses Dokuwiki verwendet ein von Anymorphic Webdesign erstelltes Thema.

Useful SQL to find out Dialed Numbers, CallType & Script of last 10 calls.

CUIC 8.5 (for ICM 8.5) Last 20 Calls - Route Call Detail Report

Note: for UCCE 9, replace DbDateTime with DateTime

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 

SELECT TOP (20)

StartDateTime = Route_Call_Detail.DateTime, 
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, 
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,
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

FROM 
Route_Call_Detail(nolock), 
Script(nolock), 
Master_Script(nolock), 
Call_Type(nolock), 
Routing_Client(nolock)

WHERE
(Route_Call_Detail.ScriptID = Script.ScriptID)
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

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(CallsOfferedToHalf)As Calls ,DateTime 
FROM Peripheral_Half_Hour
WHERE DateTime > GetDate()-1
Group BY DateTime
Order By Calls Desc


select SUM(CallsHandledHalf) AS TotalHalfCalls,DateTime  
From t_Call_Type_Half_Hour
Where DateTime > GetDate()-1
Group by DateTime
Order By TotalHalfCalls 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 t_Agent_Half_Hour
Where DateTime > Getdate()-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

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)

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
cisco/uc/icm/sql.txt · Last modified: 2014/10/13 13:30 by gerardorourke
You are here: homeciscoucicmsql
Dieses Dokuwiki verwendet ein von Anymorphic Webdesign erstelltes Thema.
www.chimeric.de Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0