This is an old revision of the document!

TCD with TCV Pivot

TCD with Agent & selected TCVs

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
;WITH MY_TCV_PVT AS
(SELECT * FROM (SELECT
 TCDRecoveryKey = TCV.TCDRecoveryKey
,ECCName = ECC.EnterpriseName
,ECCValue = TCV.ECCValue
 
FROM 
 [ucce_awdb].[dbo].[Termination_Call_Variable] TCV
,[ucce_awdb].[dbo].[Expanded_Call_Variable] ECC
 
WHERE 
TCV.DateTime >=  :start_date
AND TCV.DateTime <  :end_date
AND TCV.ExpandedCallVariableID IN (5030,5036,5034,5035,5039,5040)
AND TCV.ExpandedCallVariableID = ECC.ExpandedCallVariableID
)
AS S
PIVOT 
(MAX(ECCValue) FOR [ECCName] IN ([USER.cust.evipid],[USER.cust.mprn],[USER.cust.ivrapp],[USER.cust.ivropt],[USER.cim.activity.id],[USER.cim.activity.priority])) 
TCV_PVT)
 
SELECT TOP (8001)
 TCDRecoveryKey = TCD.RecoveryKey
,PeripheralName = Peripheral.EnterpriseName
,MY_TCV_PVT.[USER.cust.evipid]
,MY_TCV_PVT.[USER.cust.mprn]
,MY_TCV_PVT.[USER.cust.ivrapp]
,MY_TCV_PVT.[USER.cust.ivropt]
,MY_TCV_PVT.[USER.cim.activity.id]
,MY_TCV_PVT.[USER.cim.activity.priority]
,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)
  LEFT JOIN MY_TCV_PVT ON (TCD.RecoveryKey = MY_TCV_PVT.TCDRecoveryKey)
 
 WHERE 
 
  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

TCD with Peripheral Filter & selected TCVs

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
;WITH MY_TCV_PVT AS
(SELECT * FROM (SELECT
 TCDRecoveryKey = TCV.TCDRecoveryKey
,ECCName = ECC.EnterpriseName
,ECCValue = TCV.ECCValue
 
FROM 
 [ucce_awdb].[dbo].[Termination_Call_Variable] TCV
,[ucce_awdb].[dbo].[Expanded_Call_Variable] ECC
 
WHERE 
TCV.DateTime >=  :start_date
AND TCV.DateTime <  :end_date
AND TCV.ExpandedCallVariableID IN (5030,5036,5034,5035,5039,5040)
AND TCV.ExpandedCallVariableID = ECC.ExpandedCallVariableID
)
AS S
PIVOT 
(MAX(ECCValue) FOR [ECCName] IN ([USER.cust.evipid],[USER.cust.mprn],[USER.cust.ivrapp],[USER.cust.ivropt],[USER.cim.activity.id],[USER.cim.activity.priority])) 
TCV_PVT)
 
SELECT TOP (8001)
 TCDRecoveryKey = TCD.RecoveryKey
,PeripheralName = Peripheral.EnterpriseName
,MY_TCV_PVT.[USER.cust.evipid]
,MY_TCV_PVT.[USER.cust.mprn]
,MY_TCV_PVT.[USER.cust.ivrapp]
,MY_TCV_PVT.[USER.cust.ivropt]
,MY_TCV_PVT.[USER.cim.activity.id]
,MY_TCV_PVT.[USER.cim.activity.priority]
,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)
  LEFT JOIN MY_TCV_PVT ON (TCD.RecoveryKey = MY_TCV_PVT.TCDRecoveryKey)
 
 WHERE 
 
  DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC) >=  :start_date
  AND DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC) < :end_date
  AND TCD.PeripheralID IN (:PeripheralID) 
  AND TCD.ANI LIKE (:ANI)
  AND TCD.DigitsDialed LIKE (:DigitsDialed)
 
 ORDER BY 
StartDateTimeUTC , RouterCallKeyDay,   RouterCallKey,   RouterCallKeySequenceNumber

Call Type with Average Queue Time (from TCD)

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
SELECT 
--Date = CONVERT(Datetime,(CONVERT(char(10),[DateTime],102)),102)
--,TCD.CallDispositionFlag
CallTypeEnterpriseName=Call_Type.EnterpriseName
,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
--,CallTypeID=TCD.CallTypeID
,AvgLocalQueueTime=AVG(TCD.LocalQTime)
,COUNT(*) AS COUNT
 
FROM Termination_Call_Detail TCD
LEFT JOIN Call_Type (nolock) ON (TCD.CallTypeID = Call_Type.CallTypeID)
 
WHERE 
(TCD.DateTime >= '2019-07-11 00:00:00' AND TCD.DateTime <= '2019-07-11 23:59:59')
--(TCD.DateTime >= '2018-07-23 00:00:00' AND TCD.DateTime <= '2018-07-23 23:59:59')
--AND CallDispositionFlag = '2'
 
--GROUP BY CONVERT(Datetime,(CONVERT(char(10),[DateTime],102)),102), TCD.CallDispositionFlag, TCD.CallTypeID, Call_Type.EnterpriseName
GROUP BY TCD.CallDispositionFlag, Call_Type.EnterpriseName
--ORDER BY CONVERT(Datetime,(CONVERT(char(10),[DateTime],102)),102), Call_Type.EnterpriseName
--ORDER BY Call_Type.EnterpriseName
ORDER BY 
--Count DESC
Call_Type.EnterpriseName
  • vendors/cisco/uc/icm/sql/tcd-tcv.1670918583.txt.gz
  • Last modified: 2022/12/13 08:03
  • by gerardorourke