This is an old revision of the document!

UCCE Route SQL

Schema Reference:
http://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/icm_enterprise/icm_enterprise_10_5_1/Reference/Guide/UCCE_BK_DDE31A79_00_database-schema-handbook-icm-cce/UCCE_BK_DDE31A79_00_database-schema-handbook-icm-cce_chapter_011.html#UCCE_TP_R73CF0D9_00

Route Error Codes Reference:
http://docwiki.cisco.com/wiki/Router_Error_Codes

Distinct ANI Query From TCD table Collection for CUIC

SELECT DISTINCT TOP 3000 ANI AS ID, ANI AS VALUE FROM Termination_Call_Detail
WHERE DateTime > GetDate()-365

RCD Query

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 

SELECT  TOP (1000)
 RouteDateTime = Route_Call_Detail.DateTime
,CallStartDateTime = Route_Call_Detail.BeganRoutingDateTime
,BeganCallTypeDateTime = Route_Call_Detail.BeganCallTypeDateTime
,RCDRecoveryKey = Route_Call_Detail.RecoveryKey
,RCDRouterCallKeyDay = Route_Call_Detail.RouterCallKeyDay
,RCDRouterCallKey = Route_Call_Detail.RouterCallKey
,ANI = ISNULL(Route_Call_Detail.ANI,'NULL')
,DialedNumberString = ISNULL(Route_Call_Detail.DialedNumberString,'')
,CallTypeName = Call_Type.EnterpriseName
,RoutingClientName = ISNULL(Routing_Client.EnterpriseName,'')
,MasterScriptName = ISNULL(Master_Script.EnterpriseName,'')
,FinalObjectID = Route_Call_Detail.FinalObjectID
,RouteEnterpriseName = Route.EnterpriseName
,RCDLabel = Route_Call_Detail.Label
,RouterQueueTime = Route_Call_Detail.RouterQueueTime
,CASE Route_Call_Detail.RouterErrorCode 
WHEN '0' THEN 'OK'
WHEN '448' THEN 'Abandoned'
WHEN '64' THEN 'No Script Sheduled'
WHEN '66' THEN 'No Default Label'
ELSE CAST (Route_Call_Detail.RouterErrorCode AS VARCHAR(10))
END AS 'RouterErrorCode'
,CASE Route_Call_Detail.TargetType
WHEN '0' THEN 'Call routing ended badly'
WHEN '1' THEN 'DefaultRouteCall'
WHEN '2' THEN 'RouteToAgentCall'
WHEN '3' THEN 'RouteToService'
WHEN '4' THEN 'RouteToSkillGroup'
WHEN '8' THEN 'LabelNode'
WHEN '11' THEN 'MultipleLabels Call routing ended badly'
WHEN '12' THEN 'ScheduledTarget'
WHEN '13' THEN 'ResultDone - Only applicable to an AdminScript that ends with no errors'
WHEN '14' THEN 'Call Abandoned'
WHEN '15' THEN 'ReleaseNode'
WHEN '16' THEN 'QueuedTooLong'
WHEN '17' THEN 'SendAgentToAgentNode'
WHEN '18' THEN 'DynamicLabelCall'
WHEN '19' THEN 'DivertDynamicLabels'
WHEN '20' THEN 'QueuedDialogFailure'
WHEN '21' THEN 'RouteToAgentAndGroup'
WHEN '22' THEN 'RouteToPrecisionQueue'
ELSE CAST (Route_Call_Detail.TargetType AS VARCHAR(10))
END AS TargetType
,RequeryResult = Route_Call_Detail.RequeryResult
,VruProgress = Route_Call_Detail.VruProgress
,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)
INNER JOIN Call_Type(nolock)ON (Route_Call_Detail.CallTypeID = Call_Type.CallTypeID)
LEFT JOIN Script(nolock)ON(Route_Call_Detail.ScriptID = Script.ScriptID)
LEFT JOIN Master_Script(nolock)ON(Script.MasterScriptID = Master_Script.MasterScriptID)
LEFT JOIN Routing_Client(nolock)ON (Route_Call_Detail.RoutingClientID = Routing_Client.RoutingClientID)
LEFT JOIN Route (nolock) ON (Route_Call_Detail.RouteID = Route.RouteID)

WHERE
Route_Call_Detail.DateTime >= DateAdd(MINUTE,-10,GETDATE())
--Route_Call_Detail.DateTime >= '2014-11-26 00:00:00' 
--AND Route_Call_Detail.DateTime < '2014-11-26 23:59:59'

--AND (Route_Call_Detail.ANI = '865551234')
--AND (Route_Call_Detail.DialedNumberString = '55555')
--AND (Route_Call_Detail.Label = '55555')

ORDER BY Route_Call_Detail.DateTime DESC

TCD SQL Query

DECLARE @start_date DATE
DECLARE @end_date DATE
SET @start_date = GETDate()-1
SET @end_date = GetDate()
 
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
 
SELECT TOP (1000)
 
 TCDRecoveryKey = Termination_Call_Detail.RecoveryKey
--,RecordStartDateTimeUTC = Termination_Call_Detail.StartDateTimeUTC
,CallStartDateTime = DATEADD(MINUTE,-Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)
--,StartRingDateTime = DATEADD(SECOND,Termination_Call_Detail.LocalQTime,(DATEADD(MINUTE,-Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)) + (Termination_Call_Detail.LocalQTime + Termination_Call_Detail.NetQTime + Termination_Call_Detail.DelayTime)
--,StartRingDateTime = DATEADD(SECOND,Termination_Call_Detail.DelayTime,(DATEADD(SECOND,Termination_Call_Detail.NetQTime(DATEADD(SECOND,Termination_Call_Detail.LocalQTime,(DATEADD(MINUTE,-Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)))))))
,StartRingDateTime = DATEADD(SECOND,Termination_Call_Detail.LocalQTime,(DATEADD(SECOND,Termination_Call_Detail.NetQTime,(DATEADD(SECOND,Termination_Call_Detail.DelayTime,(DATEADD(MINUTE,-Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)))))))
,CallConnectDateTime  = DATEADD(SECOND,Termination_Call_Detail.RingTime,(DATEADD(SECOND,Termination_Call_Detail.LocalQTime,(DATEADD(SECOND,Termination_Call_Detail.NetQTime,(DATEADD(SECOND,Termination_Call_Detail.DelayTime,(DATEADD(MINUTE,-Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)))))))))
,CallDisconectDateTime  = DATEADD(SECOND,Termination_Call_Detail.HoldTime,(DATEADD(SECOND,Termination_Call_Detail.TalkTime,(DATEADD(SECOND,Termination_Call_Detail.RingTime,(DATEADD(SECOND,Termination_Call_Detail.LocalQTime,(DATEADD(SECOND,Termination_Call_Detail.NetQTime,(DATEADD(SECOND,Termination_Call_Detail.DelayTime,(DATEADD(MINUTE,-Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)))))))))))))
--,TEST = (DATEADD(MINUTE,-Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)) 
--,TimeZone = Termination_Call_Detail.TimeZone
,RecordEndDateTime = Termination_Call_Detail.DateTime
,PeripheralCallKey = Termination_Call_Detail.PeripheralCallKey
,TCDRouterCallKeyDay = Termination_Call_Detail.RouterCallKeyDay
,TCDRouterCallKey = Termination_Call_Detail.RouterCallKey
,ICRCallKey = Termination_Call_Detail.ICRCallKey
,ICRCallKeyParent = Termination_Call_Detail.ICRCallKeyParent
,ICRCallKeyChild = Termination_Call_Detail.ICRCallKeyChild
,RouterCallKeySequenceNumber=Termination_Call_Detail.RouterCallKeySequenceNumber
,CallTypeID=Termination_Call_Detail.CallTypeID
,CallTypeEnterpriseName=Call_Type.EnterpriseName
,ANI = Termination_Call_Detail.ANI
,DNIS = Termination_Call_Detail.DNIS
,DigitsDialed = Termination_Call_Detail.DigitsDialed
--,MRDomainEnterpriseName = Media_Routing_Domain.EnterpriseName
--,AgentSkillTargetID = ISNULL(Termination_Call_Detail.AgentSkillTargetID,'')
,AgentTeam = ISNULL(Agent_Team.EnterpriseName,'')
,AgentName = ISNULL(Person.LastName + ',' + Person.FirstName,'')
,AgentLoginName = ISNULL(Person.LoginName,'')
,AgentInstrumentPortNumber = Termination_Call_Detail.InstrumentPortNumber
--,SkillGroupSkillTargetID = Termination_Call_Detail.SkillGroupSkillTargetID
--,SkillGroupName = Skill_Group.EnterpriseName
,SkillGroup_OR_PrecisionQName = ISNULL(Precision_Queue.EnterpriseName,ISNULL(Skill_Group.EnterpriseName,''))
--,TCDPrecisionQueue = Termination_Call_Detail.PrecisionQueueID
--,PeripheralID = Termination_Call_Detail.PeripheralID
--,PrecisionQueueName = Precision_Queue.EnterpriseName
--,SourceAgentPeripheralNumber = Termination_Call_Detail.SourceAgentPeripheralNumber
--,SourceAgentSkillTargetID = Termination_Call_Detail.SourceAgentSkillTargetID
,SourceAgentName = ISNULL(SPerson.LastName + ',' + SPerson.FirstName,'')
--,[ServiceSkillTargetID]
--,TCDRouteID = Termination_Call_Detail.RouteID
,Termination_Call_Detail.PeripheralCallType
,CASE Termination_Call_Detail.PeripheralCallType
WHEN '1' THEN 'ACD IN'
WHEN '2' THEN 'Pre-Route ACD IN'
WHEN '4' THEN 'Transfer IN/OUT'
WHEN '6' THEN 'Other IN'
WHEN '7' THEN 'Auto OUT'
WHEN '9' THEN 'OUT'
WHEN '10' THEN 'Agent Inside'
WHEN '12' THEN 'Consult' 
WHEN '13' THEN 'Consult Offered' 
WHEN '15' THEN 'Conference' 
WHEN '17' THEN 'Preview'
WHEN '18' THEN 'Reserved'
WHEN '19' THEN 'Supervisor Assist' 
WHEN '20' THEN 'Emergency Call' 
WHEN '21' THEN 'Supervisor Monitor' 
WHEN '23' THEN 'Supervisor Barge In' 
WHEN '24' THEN 'Supervisor Intercept' 
WHEN '27' THEN 'Reservation Preview'
WHEN '28' THEN 'Reservation Preview Direct'
WHEN '29' THEN 'Reservation Predictive'
WHEN '30' THEN 'Reservation Callback'
WHEN '31' THEN 'Reservation Personal'
WHEN '32' THEN 'Customer Preview'
WHEN '33' THEN 'Customer Preview Direct'
WHEN '34' THEN 'Customer Predictive'
WHEN '35' THEN 'Customer Callback'
WHEN '36' THEN 'Customer Personal Callback'
WHEN '37' THEN 'Customer IVR'
WHEN '38' THEN 'Non-ACD Call Call type for Multi-Line Agent'
WHEN '39' THEN 'Play Agent Greeting' 
WHEN '40' THEN 'Record Agent Greeting'
WHEN '41' THEN 'Voice Callback'
ELSE CAST(Termination_Call_Detail.PeripheralCallType AS VARCHAR(10))
END AS PeripheralCallTypeDesc
,CallDispositionFlag = Termination_Call_Detail.CallDispositionFlag
,CASE Termination_Call_Detail.CallDispositionFlag
WHEN '1' THEN 'HANDLED'
WHEN '2' THEN 'ABANDONED'
WHEN '3' THEN 'SHORT'
WHEN '4' THEN 'ERROR'
WHEN '5' THEN 'REDIRECTED'
WHEN '6' THEN 'REQUERY'
WHEN '7' THEN 'INCOMPLETE'
ELSE CAST(Termination_Call_Detail.CallDispositionFlag AS VARCHAR(10))
END AS CallDispositionFlagDesc
,CallDisposition = Termination_Call_Detail.CallDisposition
,CASE Termination_Call_Detail.CallDisposition     
WHEN '1' THEN 'Abandoned in Network'
WHEN '3' THEN 'Abandoned Ring'
WHEN '4' THEN 'Abandoned Delay'
WHEN '6' THEN 'Abandoned On Hold'
WHEN '7' THEN 'Short Call'
WHEN '10' THEN 'Agent-initiated call was not Answered'
WHEN '13' THEN 'Handled without Transfer or Conf'
WHEN '14' THEN 'Handled by a non-agent'
WHEN '15' THEN 'Call Redirected '
WHEN '19' THEN 'RONA'
WHEN '20' THEN 'Outbound - No Ringback'
WHEN '21' THEN 'Outbound - Operator'
WHEN '22' THEN 'Outbound - No dial tone'
WHEN '23' THEN 'Outbound - Person Ans'
WHEN '24' THEN 'Outbound - FAX machine'
WHEN '26' THEN 'Outbound - Call was stopped before the customer picked up'
WHEN '27' THEN 'PIM detected an error condition or an event did not occur for a call for an extended period of time'
WHEN '28' THEN 'Blind Transfer'
WHEN '29' THEN 'Announced Transfer'
WHEN '30' THEN 'Conferenced'
WHEN '33' THEN 'Outbound - call was picked up by an answering machine'
WHEN '34' THEN 'Network Blind Transfer'
WHEN '35' THEN 'Task Abandoned in Router'
WHEN '36' THEN 'Task Abandoned Before Offered'
WHEN '37' THEN 'Task Abandoned While Offered'
WHEN '38' THEN 'Normal End Task'
WHEN '39' THEN 'Cannot Obtain Task ID'
WHEN '40' THEN 'Agent Logged Out During Task'
WHEN '41' THEN 'Maximum Task Lifetime Exceeded'
WHEN '42' THEN 'Application Path Went Down'
WHEN '52' THEN 'Called Party Disconnected'
WHEN '54' THEN 'Successful Single Step Transfer'
WHEN '55' THEN 'Network Consult Transfer'
WHEN '57' THEN 'Abandon Network Consult'
WHEN '58' THEN 'Router Requery Before Answer Router Received a Requery Event from CVP before the Agent PG indicated the call was answered by an agent.'
WHEN '59' THEN 'Router Requery After Answer Router Received a Requery Event from CVP after the Agent PG indicated the call was answered by an agent.'
WHEN '60' THEN 'Network Error Router received a Network Error for a call targeting an agent before the call arrived to the agent.'
WHEN '61' THEN 'Network Error Before Answer Router Received a Network Error Event from CVP before the Agent PG indicated the call was answered by an agent.'
WHEN '62' THEN 'Network Error After Answer'
ELSE CAST(Termination_Call_Detail.CallDisposition AS VARCHAR(10))
END AS CallDispositionDesc
,Duration = Termination_Call_Detail.Duration
,NetworkTime = Termination_Call_Detail.NetworkTime
,NetQTime = Termination_Call_Detail.NetQTime
,LocalQTime = Termination_Call_Detail.LocalQTime
,RingTime = Termination_Call_Detail.RingTime
,DelayTime = Termination_Call_Detail.DelayTime
,TimeToAband = Termination_Call_Detail.TimeToAband
,TalkTime = Termination_Call_Detail.TalkTime
,HoldTime = Termination_Call_Detail.HoldTime
,CallDuration = (Termination_Call_Detail.TalkTime+Termination_Call_Detail.HoldTime)
,ConferenceTime = Termination_Call_Detail.ConferenceTime
,WorkTime = Termination_Call_Detail.WorkTime
,WrapupData = Termination_Call_Detail.WrapupData
,CallSegmentTime = Termination_Call_Detail.CallSegmentTime
,PV1 = Termination_Call_Detail.Variable1
,PV2 = Termination_Call_Detail.Variable2
,PV3 = Termination_Call_Detail.Variable3
,PV4 = Termination_Call_Detail.Variable4
,PV5 = Termination_Call_Detail.Variable5
,PV6 = Termination_Call_Detail.Variable6
,PV7 = Termination_Call_Detail.Variable7
,PV8 = Termination_Call_Detail.Variable8
,PV9 = Termination_Call_Detail.Variable9
,PV10 = Termination_Call_Detail.Variable10
,UserToUser = Termination_Call_Detail.UserToUser
,NewTransaction = Termination_Call_Detail.NewTransaction -- Indicates there is at least one more TCD row for this call
,NetworkTargetID = Termination_Call_Detail.NetworkTargetID
,TrunkGroupID = Termination_Call_Detail.TrunkGroupID
,AgentPeripheralNumber = Termination_Call_Detail.AgentPeripheralNumber
,AnsweredWithinServiceLevel = Termination_Call_Detail.AnsweredWithinServiceLevel
,Trunk = Termination_Call_Detail.Trunk
,CED=Termination_Call_Detail.CED
--,BadCallTag=Termination_Call_Detail.BadCallTag
--,ApplicationTaskDisposition=Termination_Call_Detail.ApplicationTaskDisposition
--,ApplicationData=Termination_Call_Detail.ApplicationData
--,DbDateTime=Termination_Call_Detail.DbDateTime
--,ECCPayloadID=Termination_Call_Detail.ECCPayloadID
--,CallTypeReportingDateTime=Termination_Call_Detail.CallTypeReportingDateTime
--,RoutedSkillGroupSkillTargetID=Termination_Call_Detail.RoutedSkillGroupSkillTargetID
--,RoutedServiceSkillTargetID=Termination_Call_Detail.RoutedServiceSkillTargetID
--,RoutedAgentSkillTargetID=Termination_Call_Detail.RoutedAgentSkillTargetID
--,Originated=Termination_Call_Detail.Originated
--,CallReferenceID=Termination_Call_Detail.CallReferenceID
--,CallGUID=Termination_Call_Detail.CallGUID
--,LocationParamPKID=Termination_Call_Detail.LocationParamPKID
--,LocationParamName=Termination_Call_Detail.LocationParamName
--,PstnTrunkGroupID=Termination_Call_Detail.PstnTrunkGroupID
--,PstnTrunkGroupChannelNumber=Termination_Call_Detail.PstnTrunkGroupChannelNumber
--,NetworkSkillGroupQTime=Termination_Call_Detail.NetworkSkillGroupQTime
--,EnterpriseQueueTime=Termination_Call_Detail.EnterpriseQueueTime
--,ProtocolID=Termination_Call_Detail.ProtocolID
,CASE Termination_Call_Detail.ProtocolID
WHEN '0' THEN 'LEGACY_TDM'
WHEN '1' THEN 'JTAPI'
WHEN '2' THEN 'GED125_CVP'
WHEN '3' THEN 'GED125_IPIVR'
WHEN '4' THEN 'GED125_OTHER'
WHEN '5' THEN 'GED188_ACMI_CCX'
WHEN '6' THEN 'GED188_ACMI_CCE'
WHEN '7' THEN 'GED188_ACMI_EXPERT_ADVISOR'
WHEN '8' THEN 'GED188_ACMI_ERS'
WHEN '9' THEN 'ARI'
WHEN '10' THEN 'MEDIA_ROUTING'
WHEN '11' THEN 'Other'
ELSE CAST(Termination_Call_Detail.ProtocolID AS VARCHAR(10))
END AS ProtocolDesc
,PrecisionQueueStepOrder=Termination_Call_Detail.PrecisionQueueStepOrder
,Attributes=Termination_Call_Detail.Attributes
 
  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'
 
 ORDER BY 
 RouterCallKeyDay,   RouterCallKey,   RouterCallKeySequenceNumber,   StartDateTimeUTC

Agent Call Stats for all calls for a specific 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)
TCD = 'TCD'
-- TCDRecoveryKey = Termination_Call_Detail.RecoveryKey
--,RecordStartDateTimeUTC = Termination_Call_Detail.StartDateTimeUTC
--,CallStartDateTime = DATEADD(HOUR,Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)
--,StartRingDateTime = DATEADD(SECOND,Termination_Call_Detail.LocalQTime,(DATEADD(HOUR,Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)) + (Termination_Call_Detail.LocalQTime + Termination_Call_Detail.NetQTime + Termination_Call_Detail.DelayTime)
--,StartRingDateTime = DATEADD(SECOND,Termination_Call_Detail.DelayTime,(DATEADD(SECOND,Termination_Call_Detail.NetQTime(DATEADD(SECOND,Termination_Call_Detail.LocalQTime,(DATEADD(HOUR,Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)))))))
--,StartRingDateTime = DATEADD(SECOND,Termination_Call_Detail.LocalQTime,(DATEADD(SECOND,Termination_Call_Detail.NetQTime,(DATEADD(SECOND,Termination_Call_Detail.DelayTime,(DATEADD(HOUR,Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)))))))
--,CallConnectDateTime  = DATEADD(SECOND,Termination_Call_Detail.RingTime,(DATEADD(SECOND,Termination_Call_Detail.LocalQTime,(DATEADD(SECOND,Termination_Call_Detail.NetQTime,(DATEADD(SECOND,Termination_Call_Detail.DelayTime,(DATEADD(HOUR,Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)))))))))
--,CallDisconectDateTime  = DATEADD(SECOND,Termination_Call_Detail.HoldTime,(DATEADD(SECOND,Termination_Call_Detail.TalkTime,(DATEADD(SECOND,Termination_Call_Detail.RingTime,(DATEADD(SECOND,Termination_Call_Detail.LocalQTime,(DATEADD(SECOND,Termination_Call_Detail.NetQTime,(DATEADD(SECOND,Termination_Call_Detail.DelayTime,(DATEADD(HOUR,Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)))))))))))))
--,TEST = (DATEADD(HOUR,Termination_Call_Detail.TimeZone,Termination_Call_Detail.StartDateTimeUTC)) 
--,TimeZone = Termination_Call_Detail.TimeZone
--,RecordEndDateTime = Termination_Call_Detail.DateTime
--,PeripheralCallKey = Termination_Call_Detail.PeripheralCallKey
--,TCDRouterCallKeyDay = Termination_Call_Detail.RouterCallKeyDay
--,TCDRouterCallKey = Termination_Call_Detail.RouterCallKey
--,ICRCallKey = Termination_Call_Detail.ICRCallKey
--,ICRCallKeyParent = Termination_Call_Detail.ICRCallKeyParent
--,ICRCallKeyChild = Termination_Call_Detail.ICRCallKeyChild
--,RouterCallKeySequenceNumber=Termination_Call_Detail.RouterCallKeySequenceNumber
,CallTypeID=Termination_Call_Detail.CallTypeID
,CallTypeEnterpriseName=Call_Type.EnterpriseName
,ANI = Termination_Call_Detail.ANI
,DNIS = Termination_Call_Detail.DNIS
,DigitsDialed = Termination_Call_Detail.DigitsDialed
--,MRDomainEnterpriseName = Media_Routing_Domain.EnterpriseName
--,AgentSkillTargetID = ISNULL(Termination_Call_Detail.AgentSkillTargetID,'')
,AgentTeam = ISNULL(Agent_Team.EnterpriseName,'')
,AgentName = ISNULL(Person.LastName + ',' + Person.FirstName,'')
,AgentLoginName = ISNULL(Person.LoginName,'')
,AgentInstrumentPortNumber = Termination_Call_Detail.InstrumentPortNumber
--,SkillGroupSkillTargetID = Termination_Call_Detail.SkillGroupSkillTargetID
--,SkillGroupName = Skill_Group.EnterpriseName
--,SkillGroup_OR_PrecisionQName = ISNULL(Precision_Queue.EnterpriseName,ISNULL(Skill_Group.EnterpriseName,''))
--,TCDPrecisionQueue = Termination_Call_Detail.PrecisionQueueID
--,PeripheralID = Termination_Call_Detail.PeripheralID
--,PrecisionQueueName = Precision_Queue.EnterpriseName
--,SourceAgentPeripheralNumber = Termination_Call_Detail.SourceAgentPeripheralNumber
--,SourceAgentSkillTargetID = Termination_Call_Detail.SourceAgentSkillTargetID
,SourceAgentName = ISNULL(SPerson.LastName + ',' + SPerson.FirstName,'')
--,[ServiceSkillTargetID]
--,TCDRouteID = Termination_Call_Detail.RouteID
,Termination_Call_Detail.PeripheralCallType
,CASE Termination_Call_Detail.PeripheralCallType
WHEN '1' THEN 'ACD IN'
WHEN '2' THEN 'Pre-Route ACD IN'
WHEN '4' THEN 'Transfer IN/OUT'
WHEN '6' THEN 'Other IN'
WHEN '7' THEN 'Auto OUT'
WHEN '9' THEN 'OUT'
WHEN '10' THEN 'Agent Inside'
WHEN '12' THEN 'Consult' 
WHEN '13' THEN 'Consult Offered' 
WHEN '15' THEN 'Conference' 
WHEN '17' THEN 'Preview'
WHEN '18' THEN 'Reserved'
WHEN '19' THEN 'Supervisor Assist' 
WHEN '20' THEN 'Emergency Call' 
WHEN '21' THEN 'Supervisor Monitor' 
WHEN '23' THEN 'Supervisor Barge In' 
WHEN '24' THEN 'Supervisor Intercept' 
WHEN '27' THEN 'Reservation Preview'
WHEN '28' THEN 'Reservation Preview Direct'
WHEN '29' THEN 'Reservation Predictive'
WHEN '30' THEN 'Reservation Callback'
WHEN '31' THEN 'Reservation Personal'
WHEN '32' THEN 'Customer Preview'
WHEN '33' THEN 'Customer Preview Direct'
WHEN '34' THEN 'Customer Predictive'
WHEN '35' THEN 'Customer Callback'
WHEN '36' THEN 'Customer Personal Callback'
WHEN '37' THEN 'Customer IVR'
WHEN '38' THEN 'Non-ACD Call Call type for Multi-Line Agent'
WHEN '39' THEN 'Play Agent Greeting' 
WHEN '40' THEN 'Record Agent Greeting'
WHEN '41' THEN 'Voice Callback'
ELSE CAST(Termination_Call_Detail.PeripheralCallType AS VARCHAR(10))
END AS PeripheralCallTypeDesc
,CallDispositionFlag = Termination_Call_Detail.CallDispositionFlag
,CASE Termination_Call_Detail.CallDispositionFlag
WHEN '1' THEN 'HANDLED'
WHEN '2' THEN 'ABANDONED'
WHEN '3' THEN 'SHORT'
WHEN '4' THEN 'ERROR'
WHEN '5' THEN 'REDIRECTED'
WHEN '6' THEN 'REQUERY'
WHEN '7' THEN 'INCOMPLETE'
ELSE CAST(Termination_Call_Detail.CallDispositionFlag AS VARCHAR(10))
END AS CallDispositionFlagDesc
,CallDisposition = Termination_Call_Detail.CallDisposition
,CASE Termination_Call_Detail.CallDisposition     
WHEN '1' THEN 'Abandoned in Network'
WHEN '3' THEN 'Abandoned Ring'
WHEN '4' THEN 'Abandoned Delay'
WHEN '6' THEN 'Abandoned On Hold'
WHEN '7' THEN 'Short Call'
WHEN '10' THEN 'Agent-initiated call was not Answered'
WHEN '13' THEN 'Handled without Transfer or Conf'
WHEN '14' THEN 'Handled by a non-agent'
WHEN '15' THEN 'Call Redirected '
WHEN '19' THEN 'RONA'
WHEN '20' THEN 'Outbound - No Ringback'
WHEN '21' THEN 'Outbound - Operator'
WHEN '22' THEN 'Outbound - No dial tone'
WHEN '23' THEN 'Outbound - Person Ans'
WHEN '24' THEN 'Outbound - FAX machine'
WHEN '26' THEN 'Outbound - Call was stopped before the customer picked up'
WHEN '27' THEN 'PIM detected an error condition or an event did not occur for a call for an extended period of time'
WHEN '28' THEN 'Blind Transfer'
WHEN '29' THEN 'Announced Transfer'
WHEN '30' THEN 'Conferenced'
WHEN '33' THEN 'Outbound - call was picked up by an answering machine'
WHEN '34' THEN 'Network Blind Transfer'
WHEN '35' THEN 'Task Abandoned in Router'
WHEN '36' THEN 'Task Abandoned Before Offered'
WHEN '37' THEN 'Task Abandoned While Offered'
WHEN '38' THEN 'Normal End Task'
WHEN '39' THEN 'Cannot Obtain Task ID'
WHEN '40' THEN 'Agent Logged Out During Task'
WHEN '41' THEN 'Maximum Task Lifetime Exceeded'
WHEN '42' THEN 'Application Path Went Down'
WHEN '52' THEN 'Called Party Disconnected'
WHEN '54' THEN 'Successful Single Step Transfer'
WHEN '55' THEN 'Network Consult Transfer'
WHEN '57' THEN 'Abandon Network Consult'
WHEN '58' THEN 'Router Requery Before Answer Router Received a Requery Event from CVP before the Agent PG indicated the call was answered by an agent.'
WHEN '59' THEN 'Router Requery After Answer Router Received a Requery Event from CVP after the Agent PG indicated the call was answered by an agent.'
WHEN '60' THEN 'Network Error Router received a Network Error for a call targeting an agent before the call arrived to the agent.'
WHEN '61' THEN 'Network Error Before Answer Router Received a Network Error Event from CVP before the Agent PG indicated the call was answered by an agent.'
WHEN '62' THEN 'Network Error After Answer'
ELSE CAST(Termination_Call_Detail.CallDisposition AS VARCHAR(10))
END AS CallDispositionDesc
,Duration = Termination_Call_Detail.Duration
,NetworkTime = Termination_Call_Detail.NetworkTime
,NetQTime = Termination_Call_Detail.NetQTime
,LocalQTime = Termination_Call_Detail.LocalQTime
,RingTime = Termination_Call_Detail.RingTime
,DelayTime = Termination_Call_Detail.DelayTime
,TimeToAband = Termination_Call_Detail.TimeToAband
,TalkTime = Termination_Call_Detail.TalkTime
,HoldTime = Termination_Call_Detail.HoldTime
,CallDuration = (Termination_Call_Detail.TalkTime+Termination_Call_Detail.HoldTime)
,ConferenceTime = Termination_Call_Detail.ConferenceTime
,WorkTime = Termination_Call_Detail.WorkTime
,WrapupData = Termination_Call_Detail.WrapupData
,CallSegmentTime = Termination_Call_Detail.CallSegmentTime
--,PV1 = Termination_Call_Detail.Variable1
--,PV2 = Termination_Call_Detail.Variable2
--,PV3 = Termination_Call_Detail.Variable3
--,PV4 = Termination_Call_Detail.Variable4
--,PV5 = Termination_Call_Detail.Variable5
--,PV6 = Termination_Call_Detail.Variable6
--,PV7 = Termination_Call_Detail.Variable7
--,PV8 = Termination_Call_Detail.Variable8
--,PV9 = Termination_Call_Detail.Variable9
--,PV10 = Termination_Call_Detail.Variable10
--,UserToUser = Termination_Call_Detail.UserToUser
--,NewTransaction = Termination_Call_Detail.NewTransaction -- Indicates there is at least one more TCD row for this call
,NetworkTargetID = Termination_Call_Detail.NetworkTargetID
,TrunkGroupID = Termination_Call_Detail.TrunkGroupID
,AgentPeripheralNumber = Termination_Call_Detail.AgentPeripheralNumber
,AnsweredWithinServiceLevel = Termination_Call_Detail.AnsweredWithinServiceLevel
,Trunk = Termination_Call_Detail.Trunk
,CED=Termination_Call_Detail.CED
--,BadCallTag=Termination_Call_Detail.BadCallTag
--,ApplicationTaskDisposition=Termination_Call_Detail.ApplicationTaskDisposition
--,ApplicationData=Termination_Call_Detail.ApplicationData
--,DbDateTime=Termination_Call_Detail.DbDateTime
--,ECCPayloadID=Termination_Call_Detail.ECCPayloadID
--,CallTypeReportingDateTime=Termination_Call_Detail.CallTypeReportingDateTime
--,RoutedSkillGroupSkillTargetID=Termination_Call_Detail.RoutedSkillGroupSkillTargetID
--,RoutedServiceSkillTargetID=Termination_Call_Detail.RoutedServiceSkillTargetID
--,RoutedAgentSkillTargetID=Termination_Call_Detail.RoutedAgentSkillTargetID
--,Originated=Termination_Call_Detail.Originated
--,CallReferenceID=Termination_Call_Detail.CallReferenceID
--,CallGUID=Termination_Call_Detail.CallGUID
--,LocationParamPKID=Termination_Call_Detail.LocationParamPKID
--,LocationParamName=Termination_Call_Detail.LocationParamName
--,PstnTrunkGroupID=Termination_Call_Detail.PstnTrunkGroupID
--,PstnTrunkGroupChannelNumber=Termination_Call_Detail.PstnTrunkGroupChannelNumber
--,NetworkSkillGroupQTime=Termination_Call_Detail.NetworkSkillGroupQTime
--,EnterpriseQueueTime=Termination_Call_Detail.EnterpriseQueueTime
--,ProtocolID=Termination_Call_Detail.ProtocolID
,CASE Termination_Call_Detail.ProtocolID
WHEN '0' THEN 'LEGACY_TDM'
WHEN '1' THEN 'JTAPI'
WHEN '2' THEN 'GED125_CVP'
WHEN '3' THEN 'GED125_IPIVR'
WHEN '4' THEN 'GED125_OTHER'
WHEN '5' THEN 'GED188_ACMI_CCX'
WHEN '6' THEN 'GED188_ACMI_CCE'
WHEN '7' THEN 'GED188_ACMI_EXPERT_ADVISOR'
WHEN '8' THEN 'GED188_ACMI_ERS'
WHEN '9' THEN 'ARI'
WHEN '10' THEN 'MEDIA_ROUTING'
WHEN '11' THEN 'Other'
ELSE CAST(Termination_Call_Detail.ProtocolID AS VARCHAR(10))
END AS ProtocolDesc
--,PrecisionQueueStepOrder=Termination_Call_Detail.PrecisionQueueStepOrder
--,Attributes=Termination_Call_Detail.Attributes

  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'

 ORDER BY 
 RouterCallKeyDay,   RouterCallKey,   RouterCallKeySequenceNumber,   StartDateTimeUTC
  • vendors/cisco/uc/icm/sql/route.1531744457.txt.gz
  • Last modified: 2018/07/16 13:34
  • by gerardorourke