08-27-2017 01:05 PM - edited 03-14-2019 05:33 PM
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
Solved! Go to Solution.
08-30-2017 03:26 AM
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)
08-28-2017 11:51 AM
Any help is highly appreciated..thanks
08-28-2017 03:08 PM - edited 08-28-2017 03:11 PM
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
08-28-2017 03:13 PM
08-28-2017 03:28 PM
08-29-2017 01:03 PM
08-29-2017 01:07 PM
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
08-29-2017 03:09 PM
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.
08-29-2017 04:09 PM
08-29-2017 04:15 PM
Is it advisable to pull report using AgentTeam ??
08-29-2017 04:25 PM
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
08-29-2017 06:11 PM
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
08-30-2017 03:26 AM
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)
08-30-2017 02:45 PM
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.
08-30-2017 02:46 PM
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.
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide