Abandoned Calls in Queue

[Evgeny Bazyuk]

Hello.

I heard, that some CUIC's have this stock report "Abandoned Calls in Queue". But i don't have this one.

Can anyone share it's sql query for me? 

Thank you.

[piyush aghera]

Have a look at below SQL query from a stock report under Stock ->CCE -> CCE_AF_Historical -> Precision Queue Abandon Answer Distribution Historical report, replace precision queue IDs from your system:

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
SELECT Media = Media_Routing_Domain.EnterpriseName,
MRDomainID = Media_Routing_Domain.MRDomainID,
int1 = Bucket_Intervals.IntervalUpperBound1,
int2 = Bucket_Intervals.IntervalUpperBound2,
int3 = Bucket_Intervals.IntervalUpperBound3,
int4 = Bucket_Intervals.IntervalUpperBound4,
int5 = Bucket_Intervals.IntervalUpperBound5,
int6 = Bucket_Intervals.IntervalUpperBound6,
int7 = Bucket_Intervals.IntervalUpperBound7,
int8 = Bucket_Intervals.IntervalUpperBound8,
int9 = Bucket_Intervals.IntervalUpperBound9,
int10 = '>' + right('0'+(cast((Bucket_Intervals.IntervalUpperBound9/60)%60 as varchar(2))),2)+':'+right('0'+cast(Bucket_Intervals.IntervalUpperBound9%60 as varchar(2)),2),
PQ.EnterpriseName,
RQI.PrecisionQueueID,
RQI.BucketIntervalID,
interval = RQI.DateTime,
calls_aban = sum(RQI.CallsAbandToAgent + RQI.CallsAbandQ),
calls_handle = sum(SGI.CallsHandled),
calls_ans = sum(SGI.CallsAnswered),
delayqaban = sum(RQI.DelayQAbandTime),
answaittime = sum(SGI.AnswerWaitTime),
asa = CASE WHEN SUM(ISNULL(SGI.CallsAnswered,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGI.AnswerWaitTime,0)) * 1.0 / SUM(ISNULL(SGI.CallsAnswered,0)) END,
ans1 = sum(RQI.AnsInterval1),
ans2 = sum(RQI.AnsInterval2),
ans3 = sum(RQI.AnsInterval3),
ans4 = sum(RQI.AnsInterval4),
ans5 = sum(RQI.AnsInterval5),
ans6 = sum(RQI.AnsInterval6),
ans7 = sum(RQI.AnsInterval7),
ans8 = sum(RQI.AnsInterval8),
ans9 = sum(RQI.AnsInterval9),
ans10 = sum(RQI.AnsInterval10),
aban1 = sum(RQI.AbandInterval1),
aban2 = sum(RQI.AbandInterval2),
aban3 = sum(RQI.AbandInterval3),
aban4 = sum(RQI.AbandInterval4),
aban5 = sum(RQI.AbandInterval5),
aban6 = sum(RQI.AbandInterval6),
aban7 = sum(RQI.AbandInterval7),
aban8 = sum(RQI.AbandInterval8),
aban9 = sum(RQI.AbandInterval9),
aban10 = sum(RQI.AbandInterval10),
MaxCallsQueued = MAX(RQI.MaxCallsQueued),
MaxCallWaitTime = MAX(RQI.MaxCallWaitTime),
Attr1 = A1.EnterpriseName,
Attr2 = A2.EnterpriseName,
Attr3 = A3.EnterpriseName,
Attr4 = A4.EnterpriseName,
Attr5 = A5.EnterpriseName,
Attr6 = A6.EnterpriseName,
Attr7 = A7.EnterpriseName,
Attr8 = A8.EnterpriseName,
Attr9 = A9.EnterpriseName,
Attr10 = A10.EnterpriseName
FROM Bucket_Intervals (nolock),
Precision_Queue PQ (nolock),
Media_Routing_Domain (nolock),
(Select DateTime, PrecisionQueueID,
CallsHandled = SUM(ISNULL(CallsHandled, 0)),
CallsAnswered = SUM(ISNULL(CallsAnswered, 0)),
AnswerWaitTime = SUM(ISNULL(AnswerWaitTime, 0))
from Skill_Group_Interval (nolock)
group by PrecisionQueueID, DateTime) SGI,

Router_Queue_Interval RQI (nolock)
LEFT OUTER JOIN Attribute A1 (nolock) on A1.AttributeID = RQI.AttributeID1
LEFT OUTER JOIN Attribute A2 (nolock) on A2.AttributeID = RQI.AttributeID2
LEFT OUTER JOIN Attribute A3 (nolock) on A3.AttributeID = RQI.AttributeID3
LEFT OUTER JOIN Attribute A4 (nolock) on A4.AttributeID = RQI.AttributeID4
LEFT OUTER JOIN Attribute A5 (nolock) on A5.AttributeID = RQI.AttributeID5
LEFT OUTER JOIN Attribute A6 (nolock) on A6.AttributeID = RQI.AttributeID6
LEFT OUTER JOIN Attribute A7 (nolock) on A7.AttributeID = RQI.AttributeID7
LEFT OUTER JOIN Attribute A8 (nolock) on A8.AttributeID = RQI.AttributeID8
LEFT OUTER JOIN Attribute A9 (nolock) on A9.AttributeID = RQI.AttributeID9
LEFT OUTER JOIN Attribute A10 (nolock) on A10.AttributeID = RQI.AttributeID10
WHERE (DATEPART(dw, RQI.DateTime) in(2,3,4,5,6,7,1) and RQI.DateTime between '2019-07-18 00:00:00' and '2019-07-18 23:59:59' and convert([char], RQI.DateTime, 108) between '00:00:00' and '23:59:59') and (RQI.PrecisionQueueID IN (5036, 5037, 5038, 5080, 5088, 5089)) and (Bucket_Intervals.BucketIntervalID = RQI.BucketIntervalID)
and (PQ.PrecisionQueueID = RQI.PrecisionQueueID)
AND PQ.PrecisionQueueID = SGI.PrecisionQueueID
AND RQI.DateTime = SGI.DateTime
AND PQ.MRDomainID = Media_Routing_Domain.MRDomainID
GROUP BY RQI.PrecisionQueueID ,
PQ.EnterpriseName,
RQI.BucketIntervalID,
Bucket_Intervals.IntervalUpperBound1,
Bucket_Intervals.IntervalUpperBound2,
Bucket_Intervals.IntervalUpperBound3,
Bucket_Intervals.IntervalUpperBound4,
Bucket_Intervals.IntervalUpperBound5,
Bucket_Intervals.IntervalUpperBound6,
Bucket_Intervals.IntervalUpperBound7,
Bucket_Intervals.IntervalUpperBound8,
Bucket_Intervals.IntervalUpperBound9,
A1.EnterpriseName,
A2.EnterpriseName,
A3.EnterpriseName,
A4.EnterpriseName,
A5.EnterpriseName,
A6.EnterpriseName,
A7.EnterpriseName,
A8.EnterpriseName,
A9.EnterpriseName,
A10.EnterpriseName,
RQI.DateTime,
Media_Routing_Domain.MRDomainID,
Media_Routing_Domain.EnterpriseName
ORDER BY PQ.EnterpriseName,
RQI.BucketIntervalID,
RQI.DateTime ASC

[Evgeny Bazyuk]

Thank you.
But for this query precision queue IDs is needed, but my view "Router_Queue_Interval" is empty.
Has anyone this "Abandoned call detail activity report" report? If you have, please, share it's query.

[piyush aghera]

Do you use Precision Queue or Skill group in your environment ?  How did you try to run above query, against which DB ?

[Evgeny Bazyuk]

I use Skill group in my environment.
I wan to make report of Abandoned Calls for SkillGroups (not only counts, i need ANI of this calls). One worker saw same report in another branch of the company.

[piyush aghera]

I dont think there is a stock report which shows ANI of the abandoned calls, that should be a custom report.  however below will show you number of abandoned calls for skill group per interval, change value of SkillTargetID to your skill target IDs.

SET ANSI_WARNINGS OFF SET ARITHABORT OFF SET ARITHIGNORE ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF
SELECT
Media = Media_Routing_Domain.EnterpriseName,
MRDomainID = Media_Routing_Domain.MRDomainID,
int1 = Bucket_Intervals.IntervalUpperBound1,
int2 = Bucket_Intervals.IntervalUpperBound2,
int3 = Bucket_Intervals.IntervalUpperBound3,
int4 = Bucket_Intervals.IntervalUpperBound4,
int5 = Bucket_Intervals.IntervalUpperBound5,
int6 = Bucket_Intervals.IntervalUpperBound6,
int7 = Bucket_Intervals.IntervalUpperBound7,
int8 = Bucket_Intervals.IntervalUpperBound8,
int9 = Bucket_Intervals.IntervalUpperBound9,
Skill_Group.EnterpriseName,
Skill_Group.SkillTargetID,
Skill_Group_Interval.BucketIntervalID,
interval = Skill_Group_Interval.DateTime,
router_calls_aban = sum(Skill_Group_Interval.RouterCallsAbandToAgent + Skill_Group_Interval.RouterCallsAbandQ),
calls_handle = sum(Skill_Group_Interval.CallsHandled),
delayqaban = sum(Skill_Group_Interval.RouterDelayQAbandTime),
answaittime = sum(Skill_Group_Interval.AnswerWaitTime),
asa = CASE WHEN SUM(ISNULL(Skill_Group_Interval.CallsHandled,0)) = 0 THEN 0 ELSE sum(Skill_Group_Interval.AnswerWaitTime)/sum(Skill_Group_Interval.CallsHandled)END,
ans1 = sum(Skill_Group_Interval.RouterAnsInterval1),
ans2 = sum(Skill_Group_Interval.RouterAnsInterval2),
ans3 = sum(Skill_Group_Interval.RouterAnsInterval3),
ans4 = sum(Skill_Group_Interval.RouterAnsInterval4),
ans5 = sum(Skill_Group_Interval.RouterAnsInterval5),
ans6 = sum(Skill_Group_Interval.RouterAnsInterval6),
ans7 = sum(Skill_Group_Interval.RouterAnsInterval7),
ans8 = sum(Skill_Group_Interval.RouterAnsInterval8),
ans9 = sum(Skill_Group_Interval.RouterAnsInterval9),
ans10 = sum(Skill_Group_Interval.RouterAnsInterval10),
aban1 = sum(Skill_Group_Interval.RouterAbandInterval1),
aban2 = sum(Skill_Group_Interval.RouterAbandInterval2),
aban3 = sum(Skill_Group_Interval.RouterAbandInterval3),
aban4 = sum(Skill_Group_Interval.RouterAbandInterval4),
aban5 = sum(Skill_Group_Interval.RouterAbandInterval5),
aban6 = sum(Skill_Group_Interval.RouterAbandInterval6),
aban7 = sum(Skill_Group_Interval.RouterAbandInterval7),
aban8 = sum(Skill_Group_Interval.RouterAbandInterval8),
aban9 = sum(Skill_Group_Interval.RouterAbandInterval9),
aban10 = sum(Skill_Group_Interval.RouterAbandInterval10),
MaxCallsQueued = MAX(Skill_Group_Interval.RouterMaxCallsQueued),
MaxCallWaitTime = MAX(Skill_Group_Interval.RouterMaxCallWaitTime)
FROM Bucket_Intervals (nolock),
Skill_Group (nolock),
Skill_Group_Interval (nolock),
Media_Routing_Domain (nolock)
WHERE Skill_Group.SkillTargetID IN (6511, 6512, 6619) and (DATEPART(dw, Skill_Group_Interval.DateTime) in(2,3,4,5,6,7,1) and Skill_Group_Interval.DateTime between '2019-07-18 00:00:00' and '2019-07-18 23:59:59' and convert([char], Skill_Group_Interval.DateTime, 108) between '00:00:00' and '23:59:59') and ( Bucket_Intervals.BucketIntervalID = Skill_Group_Interval.BucketIntervalID )
and ( Skill_Group.SkillTargetID = Skill_Group_Interval.SkillTargetID )
and ( Media_Routing_Domain.MRDomainID = Skill_Group.MRDomainID)
and Skill_Group.SkillTargetID NOT IN (SELECT BaseSkillTargetID FROM Skill_Group (nolock) WHERE (Priority > 0) AND (Deleted <> 'Y'))
GROUP BY Skill_Group.SkillTargetID,
Skill_Group.EnterpriseName,
Skill_Group_Interval.BucketIntervalID,
Bucket_Intervals.IntervalUpperBound1,
Bucket_Intervals.IntervalUpperBound2,
Bucket_Intervals.IntervalUpperBound3,
Bucket_Intervals.IntervalUpperBound4,
Bucket_Intervals.IntervalUpperBound5,
Bucket_Intervals.IntervalUpperBound6,
Bucket_Intervals.IntervalUpperBound7,
Bucket_Intervals.IntervalUpperBound8,
Bucket_Intervals.IntervalUpperBound9,
Skill_Group_Interval.DateTime,
Media_Routing_Domain.EnterpriseName,
Media_Routing_Domain.MRDomainID
ORDER BY Skill_Group.EnterpriseName,
Skill_Group_Interval.BucketIntervalID,
Skill_Group_Interval.DateTime ASC

[Evgeny Bazyuk]

Thx a lot. 

But if someone knows how can i select ANI of abandoned calls, please reply.

[piyush aghera]

Ok.. then you need to create a custom report from Termination Call Details table using CallDisposition field, similar to below query:

select * from Termination_Call_Detail where
CallDisposition in ('1' , '2' , '3' , '4' , '6') and
DateTime between '2019-07-15 00:00:00' and '2019-07-21 23:59:00' and
LEN(ANI) > '4' -- removing internal transfers
order by DateTime

You need to refer below values of CallDisposition and decide which ones you want, I think you'd be interested in value 2 the most:

• 1 = Abandoned in Network
In Unified ICM, indicates the call was abandoned, or dropped, before being terminated at a target device (for instance, an ACD, IVR, Desklink, etc.).
In Unified CCE, indicates that the call was routed to an agent but it never arrived or arrived after the PIM reservation timed-out. (The default timeout is 7 seconds.) An agent will be set to Not Ready if it misses two consecutive routed calls, Peripheral Call Type will normally be two, and the Call Type ID and Network Target ID will be filled in.
In Outbound Option, this result code indicates customer phone not in service.

• 2 = Abandoned in Local Queue
In Unified ICM, indicates the call was abandoned in the ACD queue while queued to an ACD answering resources (for instance, a skill group, voice port, trunk, etc.)
In Unified CCE. Indicates that VRU Peripheral call was abandoned while in the queue (only for VRU LEG call type).
In Outbound Option, this result code indicates an outbound call was abandoned either by the customer or dialer.

• 3 = Abandoned Ring
In Unified ICM, indicates the call was abandoned while ringing at a device. For example, the caller did not wait for the call to be answered but hung up while the call was ringing.
In Unified CCE, indicates that the caller hung up while phone was ringing at the agent desktop.

• 4 = Abandoned Delay
In Unified ICM, indicates the call was abandoned without having been answered but not while ringing or in a queue. Typically, a call marked Abandoned Delay was delayed due to switch processing. Because of the delay, the caller ended up dropping the call before it could be answered.
In Unified CCE, indicates that the destination was not connected when the call terminated. This might mean that:
• The agent logged out
• The agent picked up the phone and then hung up without dialing digits.
• Route requests were logged on the Cisco Communication Manager PG that were not immediately redirected to an agent.

• 5 = Abandoned Interflow
In Unified ICM, indicates an interflow call that dropped before the call could be handled by an answering resource. Interflow calls are calls between ACDs. Abandoned Interflow is supported only by PIMs that track interflow calls. Currently, this includes only the Aspect CallCenter PIM.
Does not apply to Unified CCE.

• 6 = Abandoned Agent Terminal
In Unified ICM, indicates the call was dropped while being held at an agent device. For example, the caller is connected to an agent; the agent puts the caller on hold; the caller gets tired of waiting and hangs up.
In Unified CCE, indicates that the caller hung up while on hold on the Unified CM PG, which generally indicates a training issue for the agent. On the VRU PG with Service Control Queue reporting checked, this normally indicates caller abandoned..