Repeat Caller Template

[kumey]

Has anyone created a repeat caller CUIC template for CCE? Looking for something that allow you to specify a date range - i.e. all calls with the same ANI and distinct RCK for April 15 - April 16.

[Jameson Gagnepain]

I haven't seen a report like this before, but here's a quick stab at some basic SQL for it:

SELECT TOP 8001

TCDa.ANI,

MAX(TCDa.DateTime) AS FinalCallTime,

TCDb.DateTime AS PreviousCallTime

FROM Termination_Call_Detail TCDa

LEFT JOIN Termination_Call_Detail TCDb

ON TCDa.ANI = TCDb.ANI

AND (TCDa.RouterCallKey <> TCDb.RouterCallKey OR TCDa.RouterCallKeyDay <> TCDb.RouterCallKeyDay)

AND TCDa.DateTime>TCDb.DateTime

WHERE TCDb.ANI IS NOT NULL AND TCDa.ANI <> 'anonymous' AND TCDa.RouterCallKeySequenceNumber=0 AND TCDb.RouterCallKeySequenceNumber=0

AND (DATEPART(dw, TCDa.DateTime) in(2,3,4,5,6,7,1) and TCDa.DateTime between convert(DATETIME,'2015-04-17 00:00:00',21) and convert(DATETIME,'2015-04-17 23:59:59',21) and convert([char], TCDa.DateTime, 108) between '00:00:00' and '23:59:59')

AND (DATEPART(dw, TCDb.DateTime) in(2,3,4,5,6,7,1) and TCDb.DateTime between convert(DATETIME,'2015-04-17 00:00:00',21) and convert(DATETIME,'2015-04-17 23:59:59',21) and convert([char], TCDb.DateTime, 108) between '00:00:00' and '23:59:59')

GROUP BY TCDa.ANI, TCDb.DateTime

ORDER BY TCDa.ANI, TCDb.DateTime DESC

There's certainly a lot that could be added to this, but I figured this would be a good starting point if you're looking to develop this yourself. You'd likely need to build it as an Anonymous Block in order to reliably apply DateTime selection to both TCDa and TCDb at once... and I wouldn't recommend running it on time spans longer than a couple days as this will quite time consuming to run.

-Jameson

[kumey]

Hey thanks appreciate it

Kurt Mey

kumey@cisco.com<mailto:kumey@cisco.com>

408.894.3594

Sent from my iPad

[roberteliasson · Accepted solution]

Here's one I made a while ago to check duplicate outbound dials in CUIC using Anonymous Block.

You can just switch DigitsDialed to ANI

SELECT

Agent_Team.EnterpriseName as TeamName,

Agent.EnterpriseName as Agent,

TCD.DigitsDialed as DigitsDialed,

COUNT(TCD.DigitsDialed) as DuplicateDials,

SUM(TCD.Duration) as Duration

FROM

Termination_Call_Detail TCD

JOIN Agent ON TCD.AgentSkillTargetID = Agent.SkillTargetID

JOIN Skill_Group ON TCD.SkillGroupSkillTargetID = Skill_Group.SkillTargetID

LEFT JOIN Agent_Team_Member ON Agent_Team_Member.SkillTargetID=Agent.SkillTargetID

LEFT JOIN Agent_Team ON Agent_Team.AgentTeamID=Agent_Team_Member.AgentTeamID

Where TCD.DateTime >= :startDate

and TCD.DateTime <= :endDate

AND TCD.AgentSkillTargetID IN (:Advisor)

AND TCD.PeripheralCallType = 9

GROUP BY Agent_Team.EnterpriseName, Skill_Group.EnterpriseName, Agent.EnterpriseName, TCD.DigitsDialed

having count(TCD.DigitsDialed) > 1