Buy or Renew
Buy or Renew
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Start a conversation
698
Views
90
Helpful
2
Replies

UCCE/CUIC v12.6 -Trace Max Wait Time Caller

SANJ21
Level 1
Level 1

‎03-10-2022 09:05 AM

Our customer has a UCCE v12.5 12K solution that's in production. Looking at one of the stock CUIC reports called "Peripheral Skill Group Historical All Fields", we noticed that in the 1:30pm interval, our Max Wait Time suddenly jumped from 1 hour to over 3 hours from the previous interval then back down to 1 hour again on the next interval. I assume a caller decided to wait this long which is why it's showing that.
 
DateTime                            Max Wait Time
3/9/22 1:00:00 PM              01:04:33
3/9/22 1:30:00 PM              03:20:25
3/9/22 2:00:00 PM              01:08:11
 
Is there a way to try and trace this caller in the TCD records and what fields to look for? TIA
Labels:
2 Replies 2

TalkingScientist
Level 1
Level 1

‎03-10-2022 10:56 AM

Here you go, I call this my TCD Big Query

I set the filter to include calls from 1pm -2pm over 1 hour. Check out the Duration section about 2/3 to the right. Should show you what you want. 

 

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2022-03-09 13:00:00.000'
SET @EndDate = '2022-03-09 14:00:00.000'

SELECT
--Dates
tcd.peripheralid,
tcd.digitsdialed,
Dateadd(ss, -tcd.duration, tcd.datetime) AS StartTime,
Dateadd(second, tcd.ringtime + tcd.delaytime + tcd.localqtime,
Dateadd(s, -tcd.duration, tcd.datetime)) AS QueueTime,
tcd.datetime,
tcd.dbdatetime,
tcd.calltypereportingdatetime,
tcd.startdatetimeutc,
--Keys
tcd.recoverykey,
tcd.routercallkeyday,
tcd.routercallkey,
tcd.routercallkeysequencenumber,
tcd.icrcallkey,
tcd.icrcallkeychild,
tcd.icrcallkeyparent,
tcd.peripheralcallkey,
tcd.callguid,
tcd.callreferenceid,
tcd.locationparampkid,
tcd.locationparamname,
--Description
CASE tcd.peripheralcalltype
  WHEN 1 THEN 'ACD In Post-Route (1)'
  WHEN 2 THEN 'ACD In Pre-Route (2)'
  WHEN 3 THEN 'Pre-Route Direct Agent (3)'
  WHEN 4 THEN ' Transfer from Agent (4)'
  WHEN 5 THEN ' Overflow In (5)'
  WHEN 6 THEN ' Other In (6)'
  WHEN 7 THEN 'Auto Out (7)'
  WHEN 8 THEN 'Agent Out (8)'
  WHEN 9 THEN 'Out (9)'
  WHEN 10 THEN 'Agent Inside (10)'
  WHEN 11 THEN 'Offered (11)'
  WHEN 12 THEN ' Consult (12)'
  WHEN 13 THEN ' Consult Offered (13)'
  WHEN 14 THEN ' Consult Conference (14)'
  WHEN 15 THEN ' Conference (15)'
  WHEN 16 THEN 'Unmonitored (16)'
  WHEN 17 THEN 'Preview Out (17)'
  WHEN 18 THEN ' Reserved Out (18)'
  WHEN 19 THEN ' Supervisor Assist (19)'
  WHEN 20 THEN ' Emergency Call (20)'
  WHEN 21 THEN ' Supervisor Monitor (21)'
  WHEN 22 THEN 'Supervisor Whisper (22)'
  WHEN 23 THEN ' Supervisor Barge In (23)'
  WHEN 24 THEN ' Supervisor Intercept (24)'
  WHEN 25 THEN ' Route by Unified ICM (25)'
  WHEN 26 THEN ' Route by Application Instance (26)'
  WHEN 27 THEN ' Reservation Preview (27)'
  WHEN 28 THEN ' Reservation Preview Direct (28)'
  WHEN 29 THEN ' Reservation Predictive (29)'
  WHEN 30 THEN ' Reservation Callback (30)'
  WHEN 31 THEN ' Reservation Personal Callback (31)'
  WHEN 32 THEN ' Customer Preview (32)'
  WHEN 33 THEN ' Customer Preview Direct (33)'
  WHEN 34 THEN ' Customer Predictive (34)'
  WHEN 35 THEN ' Customer (35)'
  WHEN 36 THEN ' Customer (36)'
  WHEN 37 THEN ' Customer IVR (37)'
  WHEN 38 THEN ' Non-ACD Call (38)'
  WHEN 39 THEN ' Play Agent Greeting (39)'
  WHEN 40 THEN ' Record Agent Greeting (40)'
  WHEN 41 THEN ' Voice Callback (41)'
  WHEN 42 THEN ' Switch Leg (42)'
  WHEN 43 THEN ' VRU Leg (43)'
  ELSE 'Undefined: '
       + Cast( tcd.peripheralcalltype AS VARCHAR(2))
END                                      PeripheralCallType,
CASE tcd.calldisposition
  WHEN 1 THEN 'Aband. In Network (1)'
  WHEN 2 THEN 'Aband. In Local Queue (2)'
  WHEN 3 THEN 'Aband. Ring (3)'
  WHEN 4 THEN 'Aband. Delay (4)'
  WHEN 5 THEN 'Aband. Interflow (5)'
  WHEN 6 THEN 'Aband. Agent Terminal (6)'
  WHEN 7 THEN 'Short Call (7)'
  WHEN 8 THEN 'Busy (8)'
  WHEN 9 THEN 'Forced Busy (9)'
  WHEN 10 THEN 'Disco. Drop No Answer (10)'
  WHEN 11 THEN 'Disco. Drop No Answer (11)'
  WHEN 12 THEN 'Disco. Drop reorder (12)'
  WHEN 13 THEN 'Disco. Drop. Handled Pri. Route (13)'
  WHEN 14 THEN 'Disco. Hand. Oth. (14)'
  WHEN 15 THEN 'Redirected (15)'
  WHEN 16 THEN 'Cut Through (16)'
  WHEN 17 THEN 'Intraflow (17)'
  WHEN 18 THEN 'Interflow (18)'
  WHEN 19 THEN 'Ring No Answer (19)'
  WHEN 20 THEN 'Intercept Reorder (20)'
  WHEN 21 THEN 'Intercept Denial (21)'
  WHEN 22 THEN 'Time Out (22)'
  WHEN 23 THEN 'Voice Energy (23)'
  WHEN 24 THEN 'Non-Classified Energy Detected (24)'
  WHEN 25 THEN 'No Cut Through (25)'
  WHEN 26 THEN 'U-Abort (26)'
  WHEN 27 THEN 'Failed Software (27)'
  WHEN 28 THEN 'Blind Transfer (28)'
  WHEN 29 THEN 'Announced Transfer (29)'
  WHEN 30 THEN 'Conferenced (30)'
  WHEN 31 THEN 'Duplicate Transfer (31)'
  WHEN 32 THEN 'Unmonitored Device (32)'
  WHEN 33 THEN 'Answering Machine (33)'
  WHEN 34 THEN 'Network Blind (34)'
  WHEN 35 THEN 'Task Aband. in Router (35)'
  WHEN 36 THEN 'Task Aband. Before Offered (36)'
  WHEN 37 THEN 'Task Aband. While Offered (37)'
  WHEN 38 THEN 'Normal End Task (38)'
  WHEN 39 THEN 'Cant Obtain Task ID (39)'
  WHEN 40 THEN 'Agent Logged Out During Task (40)'
  WHEN 41 THEN 'Max. Task Lifetime Exceeded (41)'
  WHEN 42 THEN 'Application Path Went Down (42)'
  WHEN 43 THEN 'Unified ICM Routing Complete (43)'
  WHEN 44 THEN 'Unified ICM Routing Disabled (44)'
  WHEN 45 THEN 'Application Invalid MRD ID (45)'
  WHEN 46 THEN 'Application Invalid Dialog ID (46)'
  WHEN 47 THEN 'Application Duplicate Dialogue ID (47)'
  WHEN 48 THEN 'Application Invalid Invoke ID (48)'
  WHEN 49 THEN 'Application Invalid Script Selector (49)'
  WHEN 50 THEN 'Application Terminate Dialogue (50)'
  WHEN 51 THEN 'Task Ended During App. Init (51)'
  WHEN 52 THEN 'Called Party Disconnected (52)'
  WHEN 53 THEN 'Partial Call (53)'
  WHEN 54 THEN 'Drop Network Consult (54)'
  WHEN 55 THEN 'Network Consult Transfer (55)'
  WHEN 57 THEN 'Abandon Network Consult (57)'
  WHEN 58 THEN 'Router Requery Before Answer (58)'
  WHEN 59 THEN 'Router Requery After Answer (59)'
  WHEN 60 THEN 'Network Error (60)'
  WHEN 61 THEN 'Network Error Before Answer (61)'
  WHEN 62 THEN 'Network Error After Answer (62)'
  WHEN 63 THEN 'Task Transfer (63)'
  WHEN 64 THEN 'Application Disconnected (64)'
  WHEN 65 THEN 'Task Transferred on Agent Logout (65)'
  ELSE 'Undefined: '
       + Cast( tcd.calldisposition AS VARCHAR(2))
END                                      CallDisposition,
CASE tcd.calldispositionflag
  WHEN 1 THEN 'HANDLED (1)'
  WHEN 2 THEN 'ABANDONED (2)'
  WHEN 3 THEN 'SHORT (3)'
  WHEN 4 THEN 'ERROR (4)'
  WHEN 5 THEN 'REDIRECTED (5)'
  WHEN 6 THEN 'REQUERY (6)'
  WHEN 7 THEN 'INCOMPLETE (7)'
  ELSE 'Undefined: '
       + Cast(tcd.calldispositionflag AS VARCHAR(1))
END                                      CallDispositionFlag,
CASE tcd.protocolid
  WHEN 0 THEN 'LEGACY_TDM (0)'
  WHEN 1 THEN 'JTAPI (1)'
  WHEN 2 THEN 'GED125_CVP (2)'
  WHEN 3 THEN 'GED125_IPIVR (3)'
  WHEN 4 THEN 'GED125_OTHER (4)'
  WHEN 5 THEN 'GED188_ACMI_CCX (5)'
  WHEN 6 THEN 'GED188_ACMI_CCE (6)'
  WHEN 7 THEN 'GED188_ACMI_EXPERT_ADVISOR (7)'
  WHEN 8 THEN 'GED188_ACMI_ERS (8)'
  WHEN 9 THEN 'ARI (9)'
  WHEN 10 THEN 'MEDIA_ROUTING (10)'
  ELSE 'Other: '
       + Cast(tcd.protocolid AS VARCHAR(1))
END                                      ProtocolID,
tcd.newtransaction,
tcd.answeredwithinservicelevel,
tcd.priority,
tcd.badcalltag,
tcd.originated,
tcd.applicationtaskdisposition,
tcd.applicationdata,
--Paths
tcd.agentskilltargetid,
ta.enterprisename                        AS AgentName,
tcd.calltypeid,
ct.enterprisename                        AS CallType,
tcd.networktargetid,
tcd.peripheralid,
p.enterprisename                         AS Peripheral,
tcd.precisionqueueid,
pq.enterprisename                        AS PrecisionQueue,
tcd.precisionqueuesteporder,
tcd.attributes,
tcd.pstntrunkgroupid,
tcd.pstntrunkgroupchannelnumber,
tcd.routedagentskilltargetid,
ra.enterprisename                        AS RoutedAgentSkillTarget,
tcd.routedserviceskilltargetid,
sr.enterprisename                        AS RoutedServiceSkillTarget,
tcd.routedskillgroupskilltargetid,
sgr.enterprisename                       AS RoutedSkillGroupTarget,
tcd.routeid,
r.enterprisename                         AS Route,
tcd.serviceskilltargetid,
s.enterprisename                         AS ServiceSkillTarget,
tcd.skillgroupskilltargetid,
sg.enterprisename                        AS SkillGroupSkillTarget,
tcd.sourceagentskilltargetid,
sa.enterprisename                        AS SourceAgentSkillTarget,
tcd.trunkgroupid,
tg.enterprisename                        AS TrunkGroup,
tcd.trunk,
tcd.agentperipheralnumber,
ta.peripheralname                        AS AgentPeripheralName,
tcd.sourceagentperipheralnumber,
sa.peripheralname                        AS SourceAgentPeripheralName,
tcd.instrumentportnumber,
--Timing
tcd.duration,
tcd.netqtime + tcd.ringtime              AS QueueTime,
tcd.ringtime,
tcd.enterprisequeuetime,
tcd.holdtime,
tcd.localqtime,
tcd.netqtime,
tcd.networkskillgroupqtime,
tcd.networktime,
tcd.talktime,
tcd.timetoaband,
tcd.worktime,
tcd.callsegmenttime,
tcd.conferencetime,
tcd.delaytime,
--Caller
tcd.ani,
tcd.ced,
tcd.digitsdialed,
tcd.dnis,
tcd.usertouser,
tcd.variable1,
tcd.variable10,
tcd.variable2,
tcd.variable3,
tcd.variable4,
tcd.variable5,
tcd.variable6,
tcd.variable7,
tcd.variable8,
tcd.variable9,
tcd.wrapupdata,
--Misc
tcd.billrate,
tcd.recoveryday,
tcd.timezone,
tcd.eccpayloadid
--ECCData (Be careful)
--,tcv.ExpandedCallVariableID
--,ecv.Description, ecv.EnterpriseName
--,tcv.ArrayIndex
--,tcv.ECCValue
,
tcd.answeredwithinservicelevel
FROM   dbo.termination_call_detail tcd (nolock)
       LEFT JOIN dbo.agent(nolock) ta
              ON tcd.agentskilltargetid = ta.skilltargetid
       LEFT JOIN dbo.agent(nolock) sa
              ON tcd.sourceagentskilltargetid = sa.skilltargetid
       LEFT JOIN dbo.agent(nolock) ra
              ON tcd.routedagentskilltargetid = ra.skilltargetid
       LEFT JOIN dbo.call_type(nolock) ct
              ON tcd.calltypeid = ct.calltypeid
       LEFT JOIN dbo.peripheral p
              ON tcd.peripheralid = p.peripheralid
       LEFT JOIN dbo.route(nolock) r
              ON tcd.routeid = r.routeid
       LEFT JOIN dbo.service(nolock) s
              ON tcd.serviceskilltargetid = s.skilltargetid
       LEFT JOIN dbo.service(nolock) sr
              ON tcd.routedserviceskilltargetid = sr.skilltargetid
       LEFT JOIN dbo.skill_group(nolock) sg
              ON tcd.skillgroupskilltargetid = sg.skilltargetid
       LEFT JOIN dbo.skill_group(nolock) sgr
              ON tcd.routedskillgroupskilltargetid = sgr.skilltargetid
       LEFT JOIN dbo.precision_queue(nolock) pq
              ON tcd.precisionqueueid = pq.precisionqueueid
       LEFT JOIN dbo.trunk_group(nolock) tg
              ON tcd.trunkgroupid = tg.trunkgroupid
--ECC Variable (BE Careful, only uncomment Call_Variable lines if you A) know what you are doing AND B) Most importantly you have sufficently narrowed down the records
--LEFT JOIN chewy_hds.dbo.Termination_Call_Variable (NOLOCK) tcv on tcd.RecoveryKey = tcv.TCDRecoveryKey
--LEFT JOIN dbo.Expanded_Call_Variable (NOLOCK) ecv ON tcv.ExpandedCallVariableID = ecv.ExpandedCallVariableID
WHERE  tcd.datetime >= @StartDate
       AND tcd.datetime < @EndDate
       --Additional Where Clauses below here
       --and tcd.RouterCallKeyDay = 153833        
       --and tcd.RouterCallKey in (7251)
       --and tcd.ANI = '5558675309'
       --and ct.EnterpriseName like '%_Q'
       --and tcd.Variable1 like ''
       --and tcd.Variable2 like ''
       --and tcd.Variable3 like ''
       --and tcd.Variable4 like ''
       --and tcd.Variable5 like ''
       --and tcd.Variable6 like ''
       --and tcd.Variable7 like ''
       --and tcd.Variable8 like ''
       --and tcd.Variable9 like ''
       --and tcd.Variable10 like ''
       --and len(ANI) >9
       --and tcd.TalkTime <10
       AND tcd.netqtime > 3600
ORDER  BY tcd.routercallkeyday,
          tcd.routercallkey,
          tcd.routercallkeysequencenumber ASC 

 

 

TalkingScientist
Level 1
Level 1

‎03-10-2022 01:33 PM

I call this my TCD BigQuery. I added a filter for NetQTime >3600 so it should show you all calls that queued over 1 hour between 1pm and 2pm. 

 

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '2022-03-09 13:00:00.000'
SET @EndDate = '2022-03-09 14:00:00.000'

SELECT
--Dates
tcd.PeripheralID, tcd.DigitsDialed,
DATEADD(ss,-tcd.Duration,tcd.DateTime) as StartTime,
DATEADD(second, tcd.RingTime+tcd.DelayTime+tcd.LocalQTime, DATEADD(s,-tcd.Duration, tcd.DateTime)) as QueueTime,
tcd.DateTime,
tcd.DbDateTime,
tcd.CallTypeReportingDateTime,
tcd.StartDateTimeUTC,
--Keys
tcd.RecoveryKey,
tcd.RouterCallKeyDay,
tcd.RouterCallKey,
tcd.RouterCallKeySequenceNumber,
tcd.ICRCallKey,
tcd.ICRCallKeyChild,
tcd.ICRCallKeyParent,
tcd.PeripheralCallKey,
tcd.CallGUID,
tcd.CallReferenceID,
tcd.LocationParamPKID,
tcd.LocationParamName,
--Description
CASE tcd.PeripheralCallType
WHEN 1 THEN 'ACD In Post-Route (1)'
WHEN 2 THEN 'ACD In Pre-Route (2)'
WHEN 3 THEN 'Pre-Route Direct Agent (3)'
WHEN 4 THEN ' Transfer from Agent (4)'
WHEN 5 THEN ' Overflow In (5)'
WHEN 6 THEN ' Other In (6)'
WHEN 7 THEN 'Auto Out (7)'
WHEN 8 THEN 'Agent Out (8)'
WHEN 9 THEN 'Out (9)'
WHEN 10 THEN 'Agent Inside (10)'
WHEN 11 THEN 'Offered (11)'
WHEN 12 THEN ' Consult (12)'
WHEN 13 THEN ' Consult Offered (13)'
WHEN 14 THEN ' Consult Conference (14)'
WHEN 15 THEN ' Conference (15)'
WHEN 16 THEN 'Unmonitored (16)'
WHEN 17 THEN 'Preview Out (17)'
WHEN 18 THEN ' Reserved Out (18)'
WHEN 19 THEN ' Supervisor Assist (19)'
WHEN 20 THEN ' Emergency Call (20)'
WHEN 21 THEN ' Supervisor Monitor (21)'
WHEN 22 THEN 'Supervisor Whisper (22)'
WHEN 23 THEN ' Supervisor Barge In (23)'
WHEN 24 THEN ' Supervisor Intercept (24)'
WHEN 25 THEN ' Route by Unified ICM (25)'
WHEN 26 THEN ' Route by Application Instance (26)'
WHEN 27 THEN ' Reservation Preview (27)'
WHEN 28 THEN ' Reservation Preview Direct (28)'
WHEN 29 THEN ' Reservation Predictive (29)'
WHEN 30 THEN ' Reservation Callback (30)'
WHEN 31 THEN ' Reservation Personal Callback (31)'
WHEN 32 THEN ' Customer Preview (32)'
WHEN 33 THEN ' Customer Preview Direct (33)'
WHEN 34 THEN ' Customer Predictive (34)'
WHEN 35 THEN ' Customer (35)'
WHEN 36 THEN ' Customer (36)'
WHEN 37 THEN ' Customer IVR (37)'
WHEN 38 THEN ' Non-ACD Call (38)'
WHEN 39 THEN ' Play Agent Greeting (39)'
WHEN 40 THEN ' Record Agent Greeting (40)'
WHEN 41 THEN ' Voice Callback (41)'
WHEN 42 THEN ' Switch Leg (42)'
WHEN 43 THEN ' VRU Leg (43)'
ELSE 'Undefined: '+Cast( tcd.PeripheralCallType AS Varchar(2))
END PeripheralCallType,

CASE tcd.CallDisposition
WHEN 1 THEN 'Aband. In Network (1)'
WHEN 2 THEN 'Aband. In Local Queue (2)'
WHEN 3 THEN 'Aband. Ring (3)'
WHEN 4 THEN 'Aband. Delay (4)'
WHEN 5 THEN 'Aband. Interflow (5)'
WHEN 6 THEN 'Aband. Agent Terminal (6)'
WHEN 7 THEN 'Short Call (7)'
WHEN 8 THEN 'Busy (8)'
WHEN 9 THEN 'Forced Busy (9)'
WHEN 10 THEN 'Disco. Drop No Answer (10)'
WHEN 11 THEN 'Disco. Drop No Answer (11)'
WHEN 12 THEN 'Disco. Drop reorder (12)'
WHEN 13 THEN 'Disco. Drop. Handled Pri. Route (13)'
WHEN 14 THEN 'Disco. Hand. Oth. (14)'
WHEN 15 THEN 'Redirected (15)'
WHEN 16 THEN 'Cut Through (16)'
WHEN 17 THEN 'Intraflow (17)'
WHEN 18 THEN 'Interflow (18)'
WHEN 19 THEN 'Ring No Answer (19)'
WHEN 20 THEN 'Intercept Reorder (20)'
WHEN 21 THEN 'Intercept Denial (21)'
WHEN 22 THEN 'Time Out (22)'
WHEN 23 THEN 'Voice Energy (23)'
WHEN 24 THEN 'Non-Classified Energy Detected (24)'
WHEN 25 THEN 'No Cut Through (25)'
WHEN 26 THEN 'U-Abort (26)'
WHEN 27 THEN 'Failed Software (27)'
WHEN 28 THEN 'Blind Transfer (28)'
WHEN 29 THEN 'Announced Transfer (29)'
WHEN 30 THEN 'Conferenced (30)'
WHEN 31 THEN 'Duplicate Transfer (31)'
WHEN 32 THEN 'Unmonitored Device (32)'
WHEN 33 THEN 'Answering Machine (33)'
WHEN 34 THEN 'Network Blind (34)'
WHEN 35 THEN 'Task Aband. in Router (35)'
WHEN 36 THEN 'Task Aband. Before Offered (36)'
WHEN 37 THEN 'Task Aband. While Offered (37)'
WHEN 38 THEN 'Normal End Task (38)'
WHEN 39 THEN 'Cant Obtain Task ID (39)'
WHEN 40 THEN 'Agent Logged Out During Task (40)'
WHEN 41 THEN 'Max. Task Lifetime Exceeded (41)'
WHEN 42 THEN 'Application Path Went Down (42)'
WHEN 43 THEN 'Unified ICM Routing Complete (43)'
WHEN 44 THEN 'Unified ICM Routing Disabled (44)'
WHEN 45 THEN 'Application Invalid MRD ID (45)'
WHEN 46 THEN 'Application Invalid Dialog ID (46)'
WHEN 47 THEN 'Application Duplicate Dialogue ID (47)'
WHEN 48 THEN 'Application Invalid Invoke ID (48)'
WHEN 49 THEN 'Application Invalid Script Selector (49)'
WHEN 50 THEN 'Application Terminate Dialogue (50)'
WHEN 51 THEN 'Task Ended During App. Init (51)'
WHEN 52 THEN 'Called Party Disconnected (52)'
WHEN 53 THEN 'Partial Call (53)'
WHEN 54 THEN 'Drop Network Consult (54)'
WHEN 55 THEN 'Network Consult Transfer (55)'
WHEN 57 THEN 'Abandon Network Consult (57)'
WHEN 58 THEN 'Router Requery Before Answer (58)'
WHEN 59 THEN 'Router Requery After Answer (59)'
WHEN 60 THEN 'Network Error (60)'
WHEN 61 THEN 'Network Error Before Answer (61)'
WHEN 62 THEN 'Network Error After Answer (62)'
WHEN 63 THEN 'Task Transfer (63)'
WHEN 64 THEN 'Application Disconnected (64)'
WHEN 65 THEN 'Task Transferred on Agent Logout (65)'
ELSE 'Undefined: '+Cast( tcd.CallDisposition AS Varchar(2))
END CallDisposition,

CASE tcd.CallDispositionFlag
WHEN 1 THEN 'HANDLED (1)'
WHEN 2 THEN 'ABANDONED (2)'
WHEN 3 THEN 'SHORT (3)'
WHEN 4 THEN 'ERROR (4)'
WHEN 5 THEN 'REDIRECTED (5)'
WHEN 6 THEN 'REQUERY (6)'
WHEN 7 THEN 'INCOMPLETE (7)'
ELSE 'Undefined: '+Cast(tcd.CallDispositionFlag AS Varchar(1))
END CallDispositionFlag,

CASE tcd.ProtocolID
WHEN 0 THEN 'LEGACY_TDM (0)'
WHEN 1 THEN 'JTAPI (1)'
WHEN 2 THEN 'GED125_CVP (2)'
WHEN 3 THEN 'GED125_IPIVR (3)'
WHEN 4 THEN 'GED125_OTHER (4)'
WHEN 5 THEN 'GED188_ACMI_CCX (5)'
WHEN 6 THEN 'GED188_ACMI_CCE (6)'
WHEN 7 THEN 'GED188_ACMI_EXPERT_ADVISOR (7)'
WHEN 8 THEN 'GED188_ACMI_ERS (8)'
WHEN 9 THEN 'ARI (9)'
WHEN 10 THEN 'MEDIA_ROUTING (10)'
ELSE 'Other: '+Cast(tcd.ProtocolID AS Varchar(1))
END ProtocolID,

tcd.NewTransaction,
tcd.AnsweredWithinServiceLevel,
tcd.Priority,
tcd.BadCallTag,
tcd.Originated,
tcd.ApplicationTaskDisposition,
tcd.ApplicationData,
--Paths
tcd.AgentSkillTargetID, ta.EnterpriseName as AgentName,
tcd.CallTypeID, ct.EnterpriseName as CallType,

tcd.NetworkTargetID,
tcd.PeripheralID, p.EnterpriseName as Peripheral,
tcd.PrecisionQueueID, pq.EnterpriseName as PrecisionQueue,
tcd.PrecisionQueueStepOrder,
tcd.Attributes,
tcd.PstnTrunkGroupID,
tcd.PstnTrunkGroupChannelNumber,
tcd.RoutedAgentSkillTargetID, ra.EnterpriseName as RoutedAgentSkillTarget,
tcd.RoutedServiceSkillTargetID, sr.EnterpriseName as RoutedServiceSkillTarget,
tcd.RoutedSkillGroupSkillTargetID, sgr.EnterpriseName as RoutedSkillGroupTarget,
tcd.RouteID, r.EnterpriseName as Route,
tcd.ServiceSkillTargetID, s.EnterpriseName as ServiceSkillTarget,
tcd.SkillGroupSkillTargetID, sg.EnterpriseName as SkillGroupSkillTarget,
tcd.SourceAgentSkillTargetID, sa.EnterpriseName as SourceAgentSkillTarget,
tcd.TrunkGroupID, tg.EnterpriseName as TrunkGroup,
tcd.Trunk,
tcd.AgentPeripheralNumber, ta.PeripheralName as AgentPeripheralName,
tcd.SourceAgentPeripheralNumber, sa.PeripheralName as SourceAgentPeripheralName,
tcd.InstrumentPortNumber,
--Timing
tcd.Duration,
tcd.NetQTime + tcd.RingTime as QueueTime,
tcd.RingTime,
tcd.EnterpriseQueueTime,
tcd.HoldTime,
tcd.LocalQTime,
tcd.NetQTime,
tcd.NetworkSkillGroupQTime,
tcd.NetworkTime,
tcd.TalkTime,
tcd.TimeToAband,
tcd.WorkTime,
tcd.CallSegmentTime,
tcd.ConferenceTime,
tcd.DelayTime,
--Caller
tcd.ANI,
tcd.CED,
tcd.DigitsDialed,
tcd.DNIS,
tcd.UserToUser,
tcd.Variable1,
tcd.Variable10,
tcd.Variable2,
tcd.Variable3,
tcd.Variable4,
tcd.Variable5,
tcd.Variable6,
tcd.Variable7,
tcd.Variable8,
tcd.Variable9,
tcd.WrapupData,
--Misc
tcd.BillRate,
tcd.RecoveryDay,
tcd.TimeZone,
tcd.ECCPayloadID


--ECCData (Be careful)
--,tcv.ExpandedCallVariableID
--,ecv.Description, ecv.EnterpriseName
--,tcv.ArrayIndex
--,tcv.ECCValue

,tcd.AnsweredWithinServiceLevel

FROM
dbo.Termination_Call_Detail tcd (NOLOCK)
LEFT JOIN dbo.Agent(NOLOCK) ta ON tcd.AgentSkillTargetID = ta.SkillTargetID
LEFT JOIN dbo.Agent(NOLOCK) sa ON tcd.SourceAgentSkillTargetID = sa.SkillTargetID
LEFT JOIN dbo.Agent(NOLOCK) ra on tcd.RoutedAgentSkillTargetID = ra.SkillTargetID
LEFT JOIN dbo.Call_Type(NOLOCK) ct on tcd.CallTypeID = ct.CallTypeID
LEFT JOIN dbo.Peripheral p on tcd.PeripheralID = p.PeripheralID
LEFT JOIN dbo.Route(NOLOCK) r on tcd.RouteID = r.RouteID
LEFT JOIN dbo.Service(NOLOCK) s ON tcd.ServiceSkillTargetID = s.SkillTargetID
LEFT JOIN dbo.Service(NOLOCK) sr ON tcd.RoutedServiceSkillTargetID = sr.SkillTargetID
LEFT JOIN dbo.Skill_Group(NOLOCK) sg ON tcd.SkillGroupSkillTargetID = sg.SkillTargetID
LEFT JOIN dbo.Skill_Group(NOLOCK) sgr ON tcd.RoutedSkillGroupSkillTargetID = sgr.SkillTargetID
LEFT JOIN dbo.Precision_Queue(NOLOCK) pq ON tcd.PrecisionQueueID = pq.PrecisionQueueID
LEFT JOIN dbo.Trunk_Group(NOLOCK) tg ON tcd.TrunkGroupID = tg.TrunkGroupID

--ECC Variable (BE Careful, only uncomment Call_Variable lines if you A) know what you are doing AND B) Most importantly you have sufficently narrowed down the records
--LEFT JOIN chewy_hds.dbo.Termination_Call_Variable (NOLOCK) tcv on tcd.RecoveryKey = tcv.TCDRecoveryKey
--LEFT JOIN dbo.Expanded_Call_Variable (NOLOCK) ecv ON tcv.ExpandedCallVariableID = ecv.ExpandedCallVariableID

WHERE
tcd.DateTime >=@StartDate and tcd.DateTime <@EndDate
--Additional Where Clauses below here
--and tcd.RouterCallKeyDay = 153833
--and tcd.RouterCallKey in (7251)
--and tcd.ANI = '5558675309'

--and ct.EnterpriseName like '%_Q'
--and tcd.Variable1 like ''
--and tcd.Variable2 like ''
--and tcd.Variable3 like ''
--and tcd.Variable4 like ''
--and tcd.Variable5 like ''
--and tcd.Variable6 like ''
--and tcd.Variable7 like ''
--and tcd.Variable8 like ''
--and tcd.Variable9 like ''
--and tcd.Variable10 like ''
--and len(ANI) >9
--and tcd.TalkTime <10
and tcd.NetQTime > 3600

ORDER BY tcd.RouterCallKeyDay, tcd.RouterCallKey, tcd.RouterCallKeySequenceNumber asc

 

Customers Also Viewed These Support Documents