Tie together a Not Ready code to a call

[roberteliasson]

Hi,

We want to see what an agent did (what reason code used) after a specific call, so I somehow need to tie together the tables Termination_Call_Detail and Agent_Event_Detail

Is there any possibility of doing this?

Thanks in advance!

[Jameson Gagnepain]

Robert,

You always come up with such interesting reporting requests...

This one is tricky. First, there's no guarantee of agents going into Not Ready after a call. They certainly will get put to Not Ready automatically if they were Not Ready before the call, but they won't be selecting a reason code in that case.

By building a Stored Procedure, I think you could do something like this:

     For every "call" in TCD, join with 1st matching entry in AED with AED.DateTime after TCD.DateTime

Basically, the Stored Procedure would need to run the TCD query first, store the results temporarily, and then go row by row through the table of results running some kind of query like (SELECT TOP 1 Column1,Column2 FROM AED WHERE ...) with each AgentSkillTargetID/DateTime combo from the TCD. This is likely to be a pretty slow report to run, and if any Agents are not going Not Ready after calls, you will see the same Not Ready event as happening after multiple calls in the list.

I cannot think of a way to perform the above with an Anonymous Block or a standard SQL query.

-Jameson

[roberteliasson]

Hi Jameson,

Many thanks!

This query will be feeding a WFM system (Aspect) which is fed by Call Type.

You remember the query where we measure a Not Ready reasoncode 'After call work' into our AHT?

Right now, we see that the AHT is way lower in WFM and it´s because we spend more time on our customers than the "normal" AHT says.

So I somehow need to get the same AHT calculation in the Aspect query. However, the Cisco query you helped me to build is looking for Agent name instead of Call Type - and Aspect is looking for Call Type.

So this query will have to be built as a regular SQL script somehow.. Do you have any ideas to do this straight to the database and not through a Cisco report definition? Is it possible with a standard SQL query when connected directly to the db?

Thanks again!

[Jameson Gagnepain]

As I said, a standard SQL query that does this well does not seem very possible for this one. Whether run directly on the SQL server, or through CUIC, there's really no difference (besides query size limits and result size limits imposed by CUIC).

There's nothing in the tables that ties Not Ready events to calls, because there is nothing that naturally ties the two together besides time of occurrence.  I suppose you could try a simple join of AED and TCD where:

• TCD.DateTime < AED.DateTime
• AED.DateTime < TCD.DateTime + X seconds
• TCD.AgentSkillTargetID = AED.AgentSkillTargetID

Just keep in mind this will not be perfect, and will potentially be very slow to run. You will have plenty of scenarios where, depending on timing of agent actions, you could be missing events, or have too many results, or not get a full Not Ready event due to splits across intervals, etc.  You also have to worry about multiple rows in the TCD for the same call, and properly matching CallType to Agent (usually the data is on multiple row results).

In my mind, the ideal report for this would be a stored procedure which rules out bogus situations, and properly joins up consecutive Not Ready events - which I've found are often very difficult to assemble together in a standard query. With the problem of matching Agent to CallType for each call, this can be difficult to do automatically in any scenario where there are one or more transfers on the same call.

-Jameson

[roberteliasson]

Many thanks Jameson!

It looks like creating a Stored Procedure is the way to go. Even if it´s possible to create it with a SQL it will probably be very inaccurate...

However - I´ve never built a Stored Procedure and have no idea how it works.. Didn´t find anything in the user guides either but I assume it has to be uploaded to the server and then I call it from CUIC with the procedure name?

I´ll do some more research and see if I can come up with something.

[Jameson Gagnepain]

Robert,

This is something you would create on the SQL server. You will need to do it within a custom database, as Cisco will not support any UCCE database that has been modified in structure. You're not going to find information on how to write a stored procedure in any Cisco document, this is purely a matter of having the right SQL training.

As a starting point, I would recommend you read up on "Transact-SQL" (also called T-SQL), and specifically the "SQL Cursor" functionality should be useful to you for this. I would warn you though that if you don't have basic programming knowledge, this is not exactly an easy thing to jump into.

Regarding the CUIC usage of the procedure... yes, once it's created you can just put the procedure name into CUIC. That part is quite simple.

-Jameson