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
cisco/uc/icm/sql.txt · Last modified: 2014/08/12 16:34 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