(UCCE) CUIC - Interval Boundary & Agent Not Ready

[will.alvord]

From the db schema handbook:

NOT_READY: Number of seconds the agent was in the NotReady State with respect to the Media Routing Domain, from the last state change, in NOT_READY state with reason code change, or from the last interval boundary.

For example:

Time=12:10:00 Agent NotReady state transition

Time=12:11:00 Agent Ready state transition, AgentEventDetail.Duration=60

Time=12:25:00 Agent NotReady state transition

Time=12:30:00 Interval boundary change, AgentEventDetail.Duration=300

Time=13:00:00 Interval boundary change, AgentEventDetail.Duration=1800

Does that mean that if an agent enters the NotReady state at 1:45 and enters Ready state at 2:15, both the 2:00 and 2:30 intervals will reflect 15 mins?

thanks,

will

[Jameson Gagnepain]

Will,

Close. The 1:30 and 2:00 intervals would each reflect a 15 minute duration for Not Ready.

-Jameson

[will.alvord]

Sorry - that's what I had meant.

The same would hold true for not-ready reason codes as well? In the highlighted area of the attached image, you'll see 4 intervals (18:00 - 20:00). The '1-Lunch' reason code value is greater than the interval period for 2 intervals. It almost seems that the reason code values are not being split at the interval boundary.

Any thoughts on that?

[Jameson Gagnepain]

Can you provide the SQL query for that report? I think could be an issue with the calculation for that column.

-Jameson

[will.alvord]

Sure thing Jameson. It's CUIC v9.1.1.10000.117 (in case that matters), with the stock 'agent not ready historical' report (v2.0), with the stock 'rd agent not ready historical' definition.

There's no calculation - just 'a.aux1'.

The

[Jameson Gagnepain]

Will,

That report does not show Media Routing Domain. Do you have MRDs in your setup besides Cisco_Voice? EIM, for example? If an agent has activity in multiple MRDs during an interval, you might potentially see a not ready total larger than 30 minutes.

EDIT: a.aux1 IS a calculation. It's a sum of all Agent_Event_Detail.Duration for ReasonCode=1 within that particular SkillTargetID and Interval. You can see how it's defined under AuxReason(...) and AuxDetails(...) in the SQL definition.

-Jameson

[will.alvord]

Nope - just Cisco_Voice. Also, I've noticed a handful of instances where a specific interval value for a particular reason code is higher than the not ready value for the same interval. I can't fathom how that would happen. Also, if the not ready value is higher than the sum of the reason code values, what could be the cause of that? Shouldn't the sum of all reason code values equal the not ready time for the same interval?

I'm almost to the point of caving and opening a TAC case. +5 for your help, but I would really like to get to the bottom of this still.

[Jameson Gagnepain]

As for the Not Ready Value being higher than the sum of reason codes, if you have any codes defined outside of the ones in the report (0-9, 50002, 50003, 50004, 50010, 50020, 50030, 50040, 50041, 50042, 32767, 20001, 20002, 20003), they will not show in the report.

It looks like this is an issue with the way the report is built.  The NotReadyTime total is coming directly Agent_Interval, not Agent_Event_Detail, so that should be accurate. From your original screenshot, I suspect that the interval calculation in the report is wrong. It looks like the 30-minute report durations are getting rolled into one interval, while the durations less than 30 minutes get rolled into another. For example, the 18:00 Interval in your first screenshot should likely have 29:21 under Lunch, while the 20:00 interval should have 0:00:37 under Lunch.

I think the culprit is the "Interval" calculation for AuxReason. Any Agent_Event_Detail.DateTime value that's exactly at an interval boundry should be counted in the previous interval. I notice in my Agent_Event_Detail table, that sometimes when Duration=1800, the DateTime is exactly on an interval, and sometimes the DateTime is 1 second before an interval boundary.

-Jameson

[Jameson Gagnepain · Accepted solution]

In the report definition, I would update this statement (under AuxReason(...)):

CASE WHEN DATEPART(mi,DateTime) < 30 THEN '00' ELSE '30' END

And change it to this:

CASE WHEN (DATEPART(mi,DateTime) < 30 
OR (DATEPART(mi,DateTime)=30 AND DATEPART(s,DateTime)=0)) 
AND (DATEPART(mi,DateTime)<>0 OR DATEPART(s,DateTime)<>0) 
THEN '00' ELSE '30' END

Basically, this should update the Interval evaluation to force X:30:00 into the X:00:00 interval, and X:00:00 into the (X-1):30:00 interval, which is where those durations actually belong.

As it's a stock report, you will likely have to make a copy of the definition to make this change.

-Jameson

[will.alvord]

Many thanks Jameson!! I believe that you're right and the numbers look better now.

[Jameson Gagnepain]

Yes, that looks much improved.

Thanks for the +5!

-Jameson

[Jameson Gagnepain · Accepted solution]

Just realized I forgot to adjust the hour down when putting X:00:00 into the previous interval... we're not there yet it seems.

Better would be to replace the whole:

Interval=(lots of stuff),

with this:

Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>30) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 30)*30 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 30)*30 ),0) END,

Everything is looking much better on my end after this change.

-Jameson

[will.alvord]

Brilliant! I won't pretend that I completely understand it, but I do at a high level at least based on your dumbed down explanation.

Seriously, Jameson... I'm impressed by how you were able to quickly identify the issue and provide the fix. It seems that each time that I post a question/problem, you provide this same level of service. I wish that I could rate higher than 5.

[Jameson Gagnepain]

Will,

Thank you for the compliment!

The magic is this part of the CASE statement:

DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 30)*30 ),0)

The inner DATEDIFF gives the total number of minutes between the DateTime value and "0" (which is generally 1900-01-01 00:00:00.000 in SQL, but that's not important). We then divide by 30 using integer division (which rounds down). Then multiply by 30 again, and you have the number of minutes between "0" and whatever interval the DateTime value is in. The outer DATEADD turns the value back into a SQL DATETIME value.

I've been using this DATEADD/DATEDIFF trick for a little while for computing Interval in my own reports. I adapted it here by using the CASE statement to calculate the previous interval in the case of a boundary value (X:00:00 or X:30:00 time).

In any case, I'm glad to help out... I always learn something new when helping others on here.

-Jameson

[will.alvord]

Jameson,

The specific example that we looked at was corrected, but there are still a bunch of other instances which were not corrected. Any additional thoughts?