Creation of Repeat Incoming Caller report

[sarbarnes]

I have created a report based on ANI for a customer who wants to understand whether a customer has called in more than once.

However although I can run it properly and bring back data, however when I add in the DateTime field, it doesn't bring back any data!

SELECT

Agent.EnterpriseName as Agent,

Person.FirstName,

Person.LastName,

AgentName = Person.FirstName+' '+Person.LastName,

Skill_Group.EnterpriseName as SkillGroup,

Call_Type.EnterpriseName as CallType,

COUNT(TCD.ANI) as NumberofRepeatCalls,

TCD.ANI as IncomingTelNumber

FROM

Termination_Call_Detail TCD

JOIN Agent ON TCD.AgentSkillTargetID = Agent.SkillTargetID

JOIN Skill_Group ON TCD.SkillGroupSkillTargetID = Skill_Group.SkillTargetID

INNER JOIN Person ON Agent.PersonID = Person.PersonID

JOIN Call_Type ON TCD.CallTypeID = Call_Type.CallTypeID

Where TCD.DateTime >= :startDate

and TCD.DateTime <= :endDate

AND TCD.CallTypeID IN (:CallTypeName)

GROUP BY Call_Type.EnterpriseName, Skill_Group.EnterpriseName, Agent.EnterpriseName, Person.FirstName, Person.LastName, TCD.ANI

having count(TCD.ANI) > 1

I am not sure whether this is to do with the fact I am running this as an anonymous block, however I would be grateful if anyone could help to shed some light.

Thanks Sarah

[Gerry O'Rourke]

Sarah,

I would test using the SQL on the database directly, until you are confident you have your SQL 100% correct.

Only then create your CUIC report.

Is the issue above, related to your grouping?

i.e. you should not be grouping by agent, but only by calltype and ANI?

or maybe only by ANI.

If you group by ANI, Calltype AND agent, the same exact agent has to get the repeated call which is unlikely!

Gerry

[sarbarnes]

Hi Gerry, I Will try it against the DB direct, I have actually run it by Call Type as opposed to Agent, even if I take out all the agent information I still don't get any data at all!

However I did check the grouping etc..

Even if I create it as Database Query in CUIC - still get the same problem.

Any ideas?

[Gerry O'Rourke]

Sarah,

You should post your updated SQL for review.

Be careful with your JOINS etc. as you might need to do inner joins.

Some reference SQL page in below, but not directly related to what you are doing...

cisco:uc:icm:sql [Gerard O'Rourke]

Gerry

[jacparke]

Gerry makes some excellent points.  I would also point out that if you have high call volume, this is an expensive report to run.  The TCD table has an index on DateTime, but not on CallType.

To solve the multiple agent problem, you could use a virtual table to first build out the caller info and then join it to the agents which took the call.  The stock Agent Not Ready report gives a prime example of using virtual tables.

Regards,

Jack Parker

[jacparke]

When you "create the parameters" be sure to indicate that CallType is a decimal and that the two dates are date times.  When you click on the parameter tab, link the Call Type to the Call Type Value list and ensure that the Start and End Dates are marked as "Start" and "End" dates.

Please post the SQL that you get from CUIC when the report is run.  That's the "tan"ish cylinder on the report output page.

Regards,

Jack Parker

[sarbarnes]

Guys, I have taken out the agent information as the customer only wants the report run at either Call Type or Skill Group Level. This is the Database Query, which brings back the same if it was Anonymous Block with the Start end time etc in it. .

This is me running it on the actual DB which I know to have some data in it.

SELECT

TCD.DateTime,

Skill_Group.EnterpriseName as SkillGroup,

Call_Type.EnterpriseName as CallType,

COUNT(TCD.ANI) as NumberofRepeatCalls,

TCD.ANI as IncomingTelNumber

FROM

Termination_Call_Detail TCD

JOIN Skill_Group ON TCD.SkillGroupSkillTargetID = Skill_Group.SkillTargetID

JOIN Call_Type ON TCD.CallTypeID = Call_Type.CallTypeID

GROUP BY Call_Type.EnterpriseName, Skill_Group.EnterpriseName, TCD.DateTime, TCD.ANI

having count(TCD.ANI) > 1

Everything is linked as it should be.

James when I create this report, everything that needs to be linked to value lists as it should be.

Appreciate that it could be a lot of data I have already advised the customer of potential issues, however they still require the report.

Thanks Sarah

[jacparke]

I get output in CUIC with this (as interpreted by CUIC):

SELECT

Skill_Group.EnterpriseName as SkillGroup,

Call_Type.EnterpriseName as CallType,

COUNT(TCD.ANI) as NumberofRepeatCalls,

TCD.ANI as IncomingTelNumber

FROM

Termination_Call_Detail TCD

JOIN Agent ON TCD.AgentSkillTargetID = Agent.SkillTargetID

JOIN Skill_Group ON TCD.SkillGroupSkillTargetID = Skill_Group.SkillTargetID

INNER JOIN Person ON Agent.PersonID = Person.PersonID

JOIN Call_Type ON TCD.CallTypeID = Call_Type.CallTypeID

Where TCD.DateTime >= '2015-06-01 00:00:00'

and TCD.DateTime <= '2015-07-14 00:00:00'

AND TCD.CallTypeID IN (5020)

GROUP BY Call_Type.EnterpriseName, Skill_Group.EnterpriseName,TCD.ANI

having count(TCD.ANI) > 1

The where clause I fed it is:

Where TCD.DateTime >= :startDate

and TCD.DateTime <= :endDate

AND TCD.CallTypeID IN (:CallTypeName)

[sarbarnes]

James, I get data from that AB query and you can run it by date, however you do not have a datetime field to group on - which is what the customer wants, hence my question :-)

Basically if I run it for today and a customer called that call type 3 times, I can see that, however if i want to run it for a week, to see whether they called a number of times during the week, i may see the number counted over a number of days, however i don't know which days!

[jacparke]

If you want to add date into that, you could convert the DateTime to a date or some portion thereof (yy/mm) and group by that.

Regards,

Jack Parker

[Jameson Gagnepain]

Here's a couple things to consider:

• You're grouping by DateTime. This means you will only get count(TCD.ANI)>1 if you have two call legs from the same ANI with the same DateTime value. Not the result you want.
• Likewise, if you group on Agent or Person, you'll only get count(TCD.ANI)>1 if you have two call legs from the same ANI to with the same Agent.
• Ditto for Call Type, Skill Group.
• Every call will have multiple call legs to it. How are you filtering out the extra legs? Only getting legs that have both a Call Type and a Skill Group is a potential solution (i.e. where each is not NULL, or doing an INNER JOIN on the CT and SG tables), however keep in mind you you'll be missing entries for calls where the caller never reached an agent, and you'll potentially have extra entries for transfers.

-Jameson

[sarbarnes]

Jameson,

Forget the Agent part, I have removed that from the query, the key thing is that they can run it by Call Type, as the assumption is that they never got to an agent, hence they are calling back in....

I am actually thinking that the customer has the report as is which allows them to run it for yesterday for eg, and then they use another TCD report to run a filter on that particular number over a period of time, which would sort the issue.

in the meantime I will try the yy mm dd query and see where I get with that.