Differences

This shows you the differences between two versions of the page.

--- vendors:cisco:uc:icm:sql:tcd-tcv [2022/12/13 08:03] – old revision restored (2019/08/08 14:18) gerardorourke
+++ vendors:cisco:uc:icm:sql:tcd-tcv [2024/04/12 18:10] (current) – [Call Type with Average Queue Time (from TCD)] gerardorourke
@@ Line 1: / Line 1: @@
+====== TCD SQL ======
+===== TCD Example =====
+<code sql>
+ SELECT TOP (8001)
+ TCDRecoveryKey = TCD.RecoveryKey
+,PeripheralName = Peripheral.EnterpriseName
+,RecordStartDateTimeUTC = TCD.StartDateTimeUTC
+,DbDateTime=TCD.DbDateTime
+,CallStartDateTime = DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC)
+,CallConnectDateTime  = DATEADD(SECOND,TCD.RingTime,(DATEADD(SECOND,TCD.LocalQTime,(DATEADD(SECOND,TCD.NetQTime,(DATEADD(SECOND,TCD.DelayTime,(DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC)))))))))
+,CallDisconectDateTime  = DATEADD(SECOND,TCD.HoldTime,(DATEADD(SECOND,TCD.TalkTime,(DATEADD(SECOND,TCD.RingTime,(DATEADD(SECOND,TCD.LocalQTime,(DATEADD(SECOND,TCD.NetQTime,(DATEADD(SECOND,TCD.DelayTime,(DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC)))))))))))))
+,TimeZone = TCD.TimeZone
+,RecordEndDateTime = TCD.DateTime
+,PeripheralCallKey = TCD.PeripheralCallKey
+,TCDRouterCallKeyDay = TCD.RouterCallKeyDay
+,TCDRouterCallKey = TCD.RouterCallKey
+,TCDRouterCallKeys = CONCAT(TCD.RouterCallKeyDay,'-',TCD.RouterCallKey)
+,ICRCallKey = TCD.ICRCallKey
+,ICRCallKeyParent = TCD.ICRCallKeyParent
+,ICRCallKeyChild = TCD.ICRCallKeyChild
+,RouterCallKeySequenceNumber=TCD.RouterCallKeySequenceNumber
+,CallTypeID=TCD.CallTypeID
+,CallTypeEnterpriseName=Call_Type.EnterpriseName
+,ANI = TCD.ANI
+,DNIS = TCD.DNIS
+,DigitsDialed = TCD.DigitsDialed
+,MRDomainEnterpriseName = Media_Routing_Domain.EnterpriseName
+,AgentSkillTargetID = ISNULL(TCD.AgentSkillTargetID,'')
+,AgentTeamID = ISNULL(Agent_Team.AgentTeamID,'')
+,AgentTeam = ISNULL(Agent_Team.EnterpriseName,'')
+,AgentName = ISNULL(Person.LastName + ',' + Person.FirstName,'')
+,AgentLoginName = ISNULL(Person.LoginName,'')
+,AgentInstrumentPortNumber = TCD.InstrumentPortNumber
+,SkillGroupSkillTargetID = TCD.SkillGroupSkillTargetID
+,SkillGroupName = Skill_Group.EnterpriseName
+,SkillGroup_OR_PrecisionQName = ISNULL(Precision_Queue.EnterpriseName,ISNULL(Skill_Group.EnterpriseName,''))
+,TCDPrecisionQueue = TCD.PrecisionQueueID
+,PeripheralID = TCD.PeripheralID
+,PrecisionQueueName = Precision_Queue.EnterpriseName
+,SourceAgentPeripheralNumber = TCD.SourceAgentPeripheralNumber
+,SourceAgentSkillTargetID = TCD.SourceAgentSkillTargetID
+,SourceAgentName = ISNULL(SPerson.LastName + ',' + SPerson.FirstName,'')
+,TCDRouteID = TCD.RouteID
+,TCD.PeripheralCallType
+,CASE TCD.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'
+WHEN '42' THEN 'Switch Leg'
+WHEN '43' THEN 'VRU Leg'
+ELSE CAST(TCD.PeripheralCallType AS VARCHAR(10))
+END AS PeripheralCallTypeDesc
+,CallDispositionFlag = TCD.CallDispositionFlag
+,CASE TCD.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(TCD.CallDispositionFlag AS VARCHAR(10))
+END AS CallDispositionFlagDesc
+,CallDisposition = TCD.CallDisposition
+,CASE TCD.CallDisposition
+WHEN '1' THEN 'Abandoned in Network'
+WHEN '2' THEN 'Abandoned in Local Queue'
+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(TCD.CallDisposition AS VARCHAR(10))
+END AS CallDispositionDesc
+,Duration = TCD.Duration
+,NetworkTime = TCD.NetworkTime
+,NetQTime = TCD.NetQTime
+,LocalQTime = TCD.LocalQTime
+,RingTime = TCD.RingTime
+,DelayTime = TCD.DelayTime
+,TimeToAband = TCD.TimeToAband
+,TalkTime = TCD.TalkTime
+,HoldTime = TCD.HoldTime
+,CallDuration = (TCD.TalkTime+TCD.HoldTime)
+,ConferenceTime = TCD.ConferenceTime
+,WorkTime = TCD.WorkTime
+,WrapupData = TCD.WrapupData
+,CallSegmentTime = TCD.CallSegmentTime
+,PV1 = TCD.Variable1
+,PV2 = TCD.Variable2
+,PV3 = TCD.Variable3
+,PV4 = TCD.Variable4
+,PV5 = TCD.Variable5
+,PV6 = TCD.Variable6
+,PV7 = TCD.Variable7
+,PV8 = TCD.Variable8
+,PV9 = TCD.Variable9
+,PV10 = TCD.Variable10
+--,UserToUser = TCD.UserToUser
+,NewTransaction = TCD.NewTransaction -- Indicates there is at least one more TCD row for this call
+,NetworkTargetID = TCD.NetworkTargetID
+,TrunkGroupID = TCD.TrunkGroupID
+,AgentPeripheralNumber = TCD.AgentPeripheralNumber
+,AnsweredWithinServiceLevel = TCD.AnsweredWithinServiceLevel
+,Trunk = TCD.Trunk
+,CED=TCD.CED
+,ApplicationTaskDisposition=TCD.ApplicationTaskDisposition
+,ApplicationData=TCD.ApplicationData
+,ECCPayloadID=TCD.ECCPayloadID
+,CallTypeReportingDateTime=TCD.CallTypeReportingDateTime
+,RoutedSkillGroupSkillTargetID=TCD.RoutedSkillGroupSkillTargetID
+,RoutedServiceSkillTargetID=TCD.RoutedServiceSkillTargetID
+,RoutedAgentSkillTargetID=TCD.RoutedAgentSkillTargetID
+,Originated=TCD.Originated
+,CallReferenceID=TCD.CallReferenceID
+,CallGUID=TCD.CallGUID
+,LocationParamPKID=TCD.LocationParamPKID
+,LocationParamName=TCD.LocationParamName
+,PstnTrunkGroupID=TCD.PstnTrunkGroupID
+,PstnTrunkGroupChannelNumber=TCD.PstnTrunkGroupChannelNumber
+,NetworkSkillGroupQTime=TCD.NetworkSkillGroupQTime
+,EnterpriseQueueTime=TCD.EnterpriseQueueTime
+,ProtocolID=TCD.ProtocolID
+,CASE TCD.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(TCD.ProtocolID AS VARCHAR(10))
+END AS ProtocolDesc
+,PrecisionQueueStepOrder=TCD.PrecisionQueueStepOrder
+,Attributes=TCD.Attributes
+  FROM
+  Termination_Call_Detail (nolock) TCD
+  LEFT JOIN Peripheral (nolock) ON (TCD.PeripheralID = Peripheral.PeripheralID)
+  LEFT JOIN Media_Routing_Domain (nolock) ON (TCD.MRDomainID = Media_Routing_Domain.MRDomainID)
+  LEFT JOIN Agent (nolock) ON (TCD.AgentSkillTargetID = Agent.SkillTargetID)
+  LEFT JOIN Agent SAgent (nolock) ON (TCD.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 (TCD.SkillGroupSkillTargetID = Skill_Group.SkillTargetID)
+  LEFT JOIN Precision_Queue (nolock) ON (TCD.PrecisionQueueID = Precision_Queue.PrecisionQueueID)
+  LEFT JOIN Call_Type (nolock) ON (TCD.CallTypeID = Call_Type.CallTypeID)
+ WHERE
+(TCD.DateTime >= '2022-12-13 00:00:00' AND TCD.DateTime <= '2022-12-13 23:59:59')
+  -- More useful Filters commented out below in "CUIC" format
+  --DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC) >=  :start_date
+  --AND DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC) < :end_date
+  --AND TCD.PeripheralID IN (5000)
+  --AND TCD.AgentSkillTargetID IN (:AgentID)
+  --AND TCD.ANI LIKE (:ANI)
+  --AND TCD.DigitsDialed LIKE (:DigitsDialed)
+ ORDER BY
+StartDateTimeUTC , RouterCallKeyDay,   RouterCallKey,   RouterCallKeySequenceNumber
+</code>
 ====== TCD with TCV Pivot ======
 ===== TCD with Agent & selected TCVs ======
@@ Line 556: / Line 788: @@
 --Count DESC
 Call_Type.EnterpriseName
+</code>
+===== TCD - Number of Calls for a specific interval and Total Duration of calls in seconds and also in minutes =====
+<code sql>
+SELECT
+ NumOfCalls = Count(*)
+,DurationSec = SUM(Duration)
+,DurationMin = SUM(Duration)/60
+FROM Termination_Call_Detail TCD
+WHERE
+(TCD.DateTime >= '2024-01-01 00:00:00' AND TCD.DateTime <= '2024-01-31 23:59:59')
+AND TCD.PeripheralCallType = '42'
+AND TCD.DigitsDialed = '15551234'
 </code>