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

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) 

ORDER BY 
Route_Call_Detail.DbDateTime DESC

Dialled Number <> Call Type Mapping

SELECT     

DialedNumberString = Dialed_Number.DialedNumberString,
RoutingClientName = Routing_Client.EnterpriseName, 
CAllTYpeName = Call_Type.EnterpriseName

FROM         

Dialed_Number_Map,
Routing_Client,
CallType

WHERE 

(Dialed_Number_Map.DialedNumberID = Dialed_Number.DialedNumberID) AND 
(Dialed_Number.RoutingClientID = Routing_Client.RoutingClientID) AND 
(Dialed_Number_Map.CallTypeID = Call_Type.CallTypeID)

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

FROM         
Agent_Real_Time INNER JOIN
Agent ON Agent_Real_Time.SkillTargetID = Agent.SkillTargetID INNER JOIN
Person ON Agent.PersonID = Person.PersonID

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