UCCE 8.5 CUIC REPORT

[Srinivas Divakar K]

Problem: According to above custom Query and result, I noticed (i.e. starting from ROW 5 - highlighted in red) that next call start time kicks off soon/before previous call End time. Please advise what’s wrong I am doing here.

i have attached custom query please review and assist.

regards,

srinivas

[Srinivas Divakar K]

Any help is highly appreciated..thanks

[geoff]

I simplified the query a bit for my test since there is a lot of stuff we don't need to see:

 SELECT datename(m, DateTime) AS Month
	,datepart(d, DateTime) AS Day
	,datepart(hh, DateTime) AS Hour
	,CAST(RouterCallKey AS varchar) + '-' + CAST(RouterCallKeySequenceNumber AS varchar) AS 'RCK-RCKSN'
	,StartDateTimeUTC
	,CallStartTime = dateadd(s,-Duration,DateTime)
	,CallEndTime = DateTime
	,AgentSkillTargetID
FROM Termination_Call_Detail
WHERE AgentSkillTargetID IS NOT NULL
AND RouterCallKey > 0
AND DateTime >= '2017-08-22 17:13:23'
ORDER BY RouterCallKeyDay, RouterCallKey, RouterCallKeySequenceNumber 

and I can produce the same anomalies. But the RCK-RCKSN gives you the clue as to why.

 

 You can see the anomaly when it's the same call with different RCKSNs. Otherwise it's fine. This is probably what you are seeing, when more than 1 agent is involved. Why don't you add in something like I have and check the RCK, RCKSN.

PS: my server is in UTC. Start Time from the DB and through your calculation are the same.

Regards,
Geoff

[Srinivas Divakar K]

sure geoff

[Srinivas Divakar K]

FYI: customer is pulling CUIC report using agent team ..

[Srinivas Divakar K]

[Srinivas Divakar K]

Hi Geoff,

Customer would like to know why  Agent: 190149 ( happening to all Agents) 2nd call start time kicks off soon/before previous call End time. 

I ran query and attached the same. please review and advise.

I truly appreciate you for looking at this problem and assisting me.

Regards,

Srinivas

[Chintan Gajjar]

I see you are looking for start and end time for a call to an agent (please forgive me but this new look of forum is not digestive to me and it takes lot of time in getting to know whats the latest reply).

So if above is the case, i recommend to use sum of

TerminationCallDetail.TalkTime+ TerminationCallDetail.HoldTime + TerminationCallDetail.RingTime

instead to TerminationCallDetail.Duration to calculate the start and end time of agent leg.

TerminationCallDetail.Duration is cumulative and also includes the queue time + wrapup time for the agent leg.

[Srinivas Divakar K]

Thanks for your reply .


As per my understanding,


Actual Call start time = ??? ( End User calling time)

Actual Call End time = ??? ( End User – Call disconnected / Hang up time )

Agent Call Start Time = dateadd(s,- (TerminationCallDetail.TalkTime+
TerminationCallDetail.HoldTime + TerminationCallDetail.RingTime ),DateTime)

Agent Call End time = TerminationCallDetail.DateTime


Please advise.


Regards,

Srinivas

[Srinivas Divakar K]

Is it advisable to pull report using AgentTeam ??

[geoff]

Processing the TCDs is not easy.

You can see that there are multiple entries in the TCD for the same RCK, identified by the RCKSN, for the same agent. You need to discover why these multiple legs occur.

Because you are filtering the TCD where AgentID is not null, you are not seeing all the legs of the call that comprise the one call. Perhaps you should look at one call (one RCK) and see what all those other legs are and what the agent is doing to cause these multiple legs.

If we look at the screen shot you posted it seems like the row with the lowest RCKSN is the "envelope" for the call. It contains the earliest start time and the latest end time. Possibly there is something in that leg (one of the columns) that allows you to always find just this leg and ignore the others.

Regards,
Geoff

[Srinivas Divakar K]

Hi Geoff,

I am trying to understand the exact formula to get Call Start time & End Time .

I ran below queries and trying to understand why  RouterCallKey = '4091'  start time -(i.e2017-08-24 11:48:52.213) overlapping  with RouterCallKey = '3718' end time (i.e. 2017-08-24 11:50:51.193). How New Call started for Agent:190264 when his/her previous call has not ended yet.

Do i need to adjust formula to get exact CallStartTime ?

Noticed: RouterCallKey = '3718' - Agent 191342 Xferred Caller to Agent 190264.

select

CAST(RouterCallKey AS varchar) + '-' + CAST(RouterCallKeySequenceNumber AS varchar) AS 'RCK-RCKSN' ,

DigitsDialed,

ANI,

AgentPeripheralNumber AS AgentExtn,

SkillGroupSkillTargetID AS SkillTargetID,

CallDisposition,

dateadd(s,-Duration,DateTime) AS StartDateTime,

DateTime AS CallEndTime,

StartDateTimeUTC,

Duration,

TalkTime,

RingTime,

DelayTime,

HoldTime,

WorkTime

from Termination_Call_Detail a FULL OUTER JOIN Skill_Group c ON a.SkillGroupSkillTargetID = c.SkillTargetID

where  (RouterCallKey = '4091' OR RouterCallKey = '3718')   and DateTime >='2017-08-24 11:00:00'and DateTime <='2017-08-24 13:00:00'

ORDER BY RouterCallKeyDay, RouterCallKey, RouterCallKeySequenceNumber

select

CAST(RouterCallKey AS varchar) + '-' + CAST(RouterCallKeySequenceNumber AS varchar) AS 'RCK-RCKSN' ,

DigitsDialed,

ANI,

AgentPeripheralNumber AS AgentExtn,

SkillGroupSkillTargetID AS SkillTargetID,

CallDisposition,

dateadd(s,-Duration,DateTime) AS StartDateTime,

DateTime AS CallEndTime,

StartDateTimeUTC,

Duration,

TalkTime,

RingTime,

DelayTime,

HoldTime,

WorkTime

from Termination_Call_Detail a FULL OUTER JOIN Skill_Group c ON a.SkillGroupSkillTargetID = c.SkillTargetID

where  AgentPeripheralNumber = '190264' and DateTime >='2017-08-24 11:40:00'and DateTime <='2017-08-24 12:00:00'

ORDER BY RouterCallKeyDay, RouterCallKey, RouterCallKeySequenceNumber

 

[Chintan Gajjar · Accepted solution]

My Comments:

Actual Call start time = ideally the DateTime what you see on RouterCallKeySequenceNumber 0 in TCD should hold this.

But in my case its not, i always see the DateTime on RCKSN 1 (VRU label leg) hoding the DateTime accurate and lower than RCKSN 0. which makes me to think the bug here listed is true https://bst.cloudapps.cisco.com/bugsearch/bug/CSCsa68314/?referring_site=bugquickviewredir

So its best to get Start Time from Route_Call_Detail.DateTime for the same pair of RouterCallKey and RouterCallKeyDay
Actual Call End time(without wrap up) = DateTime on the agent leg of the TCD - wrapup time on the same leg.

Agent Call Start Time = dateadd(s,- (TerminationCallDetail.TalkTime+
TerminationCallDetail.HoldTime + TerminationCallDetail.RingTime ),DateTime) (valid only for agent leg)

Agent Call End time = TerminationCallDetail.DateTime - worktime (valid only for agent leg)

[Srinivas Divakar K]

thank you so much for assisting me in this regard.

i tried this Agent Call Start Time = dateadd(s,- (TerminationCallDetail.TalkTime+ TerminationCallDetail.HoldTime + TerminationCallDetail.RingTime + TerminationCallDetail.DelayTime),DateTime) (valid only for agent leg) -- its working as expected..

want to thank geoff and Chintan for all yourtime.

[Srinivas Divakar K]

thank you so much for assisting me in this regard.

i tried this Agent Call Start Time = dateadd(s,- (TerminationCallDetail.TalkTime

---

@Chintan Gajjar wrote:

My Comments:

Actual Call start time = ideally the DateTime what you see on RouterCallKeySequenceNumber 0 in TCD should hold this.

But in my case its not, i always see the DateTime on RCKSN 1 (VRU label leg) hoding the DateTime accurate and lower than RCKSN 0. which makes me to think the bug here listed is true https://bst.cloudapps.cisco.com/bugsearch/bug/CSCsa68314/?referring_site=bugquickviewredir

So its best to get Start Time from Route_Call_Detail.DateTime for the same pair of RouterCallKey and RouterCallKeyDay
Actual Call End time(without wrap up) = DateTime on the agent leg of the TCD - wrapup time on the same leg.

Agent Call Start Time = dateadd(s,- (TerminationCallDetail.TalkTime+
TerminationCallDetail.HoldTime + TerminationCallDetail.RingTime ),DateTime) (valid only for agent leg)

Agent Call End time = TerminationCallDetail.DateTime - worktime (valid only for agent leg)

---

+ TerminationCallDetail.HoldTime + TerminationCallDetail.RingTime + TerminationCallDetail.DelayTime),DateTime) (valid only for agent leg) -- its working as expected..

want to thank geoff and Chintan for all yourtime.