CUIC 9.x - Measuring AHT combined with Not Ready code

Hello everyone,

We went live with UCCE 9.x a week ago, and I´m having some difficulties with measuring AHT in a correct way.

Right now, when our agents needs to do some after call-work, they put themselves on Not Ready with a reason code called "After call work".

When they´re done with that - they put themselves back on Ready.

But this reason code is never calculated into the ACW.

How do you report your AHT?

I´ve tried to combine the stock query"Agent Team Historical All Fields" with the query from "Agent Not Ready Detail" but I just can´t get it sorted. I would really appreciate some help here if this is possible.

Thanks a lot in advance!

Regards,

Robert

Hi Robert,

     %AHT (Average Handle Time Percentage) = Sum of Talk Time / Total number of Calls / interval length (in seconds) * 100

I would request you to refer Hold tim in Agent & Agent Skil group reports - Community - Cisco Developer Community

for related information.

Thanks and Regards,

Geevarghese

Hi Geevarghese,

Thanks for your reply - I appreciate it!

I´m aware of AHT is calculated as default - but we really require to merge the default AHT with Not Ready reasoncode 71.

The reason why is that we want to know how much the total AHT is, including the not ready state where the agent had to use the Not Ready state '71' to finish the case.

Thanks again!

Robert,

What is your reasoning for not using the built-in wrapup mode? Wrapup time would be much simpler from a reporting standpoint than a specific Not Ready reason code, as it's already included in stock AHT reporting.

-Jameson

Hi Jameson

When selecting "Work not ready" we are automatically set to "Not ready" with reasoncode [0] Undefined.

So after the set (in our case) 20 seconds wrapup, the state changes to Not ready..

Is there any function we are missing here? I would really appreciate an explanation on how it should work, and I´ll take it with our Cisco team.

Thanks!

Robert,

How are you selecting "Work not ready"? What agent desktop are you using?

There are two types of wrapup:

• Work Ready - automatically applied after finishing a call that started while "Ready". After the Work Ready state, agent will automatically go back to Ready.
• Work Not Ready - automatically applied after finishing a call that started while "Not Ready". After the Work Not Ready state, agent will automatically go back to Not Ready.

-Jameson

Hi Jameson,

We´re using CAD 8.5.4.33.

With the current set up we have, unfortunately I dont see any other option right now than to include the ReasonCode 71.

Would it be hard to get this figure into the "Agent Team Historical All Fields" report definition?

Thanks!

Robert

Robert,

I've personally never had a chance to use CAD, but looking at the user guide page 21, I see this:

http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/crs/express_8_5/user/guide/cad85ccxug…

Work Ctrl+Y

Changes your state to Work, indicating

that you are in wrap-up work after

terminating a call and, when finished, will

be available to receive routed calls

As I outlined before, the system should only put an agent in Work Not Ready if they were Not Ready before the call. If an agent was Ready before the call, the system should put them in Work Ready when they select the Work button, which would automatically go back to Ready after wrap-up time is up.

Adding calculations around Reason Code 71 is certainly possible if you have access to editing Report Definitions. You would have to grab data from the Agent_Event_Detail table. The thing that makes this more difficult, is you would likely have to write the SQL as an anonymous block, not a normal query. This is largely due to the need to summarize data from the Agent_Event_Detail table before joining it with the various tables in the Agent Team Historical All Fields report.

If you've got an issue with Work Ready mode functioning, I would endeavor to correct that... perhaps an issue for TAC.

-Jameson

Jameson,

Thanks for your reply!

I get your point - but the only workaround I see is to create a custom report definition.

I´ve started with it but it shows really strange figures for "Calls handled" - anyone who want to help me with this?

SELECT

   A.EnterpriseName,

  aed.SkillTargetID,

(t9.CallsHandled * 1.0) as CallsHandled,

  aed.LoginDateTime,

  Event = CASE Event WHEN 1 THEN 'Login (1)' WHEN 2 THEN 'Logout(2)' WHEN 3 THEN 'NotReady (3)' END ,

  EventStart = DATEADD(ss,-Duration,aed.DateTime),

  EventEnd = aed.DateTime,

  aed.Duration,

  Reason = rc.ReasonText + '(' + CONVERT(varchar(10),rc.ReasonCode) + ')',

  aed.ReasonCode

FROM Agent_Event_Detail aed

LEFT OUTER JOIN Reason_Code rc on aed.ReasonCode = rc.ReasonCode

JOIN Agent A on aed.SkillTargetID = A.SkillTargetID

join Agent_Skill_Group_Interval t9 on t9.SkillTargetID = aed.SkillTargetID

WHERE  aed.SkillTargetID IN (:AgentSkillTargetID)

and aed.DateTime >= :startDate

and aed.DateTime < :endDate

and aed.ReasonCode = '71'

group by

aed.LoginDateTime,

t9.CallsHandled,

rc.ReasonCode,

aed.ReasonCode,

rc.ReasonText,

aed.Duration,

aed.DateTime,

aed.Event,

CONVERT(VARCHAR(10), aed.DateTime, 120) ,

A.EnterpriseName

,aed.SkillTargetID,A.SkillTargetID

Robert,

You're likely getting a log of extra rows of data... you don't have anything limiting the DateTime range of Agent_Skill_Group_Interval, and you have nothing tying the Agent_Event_Detail records to your intervals either.

I don't understand why you're doing a GROUP BY statement, as you're not using any aggregate functions yet (SUM, MAX, COUNT, etc).

All the data in Agent_Event_Detail will need to be summarized in a separate query before joining with your other tables. Something along the lines of this:

SELECT

ColumnName1,

ColumnName2,

ColumnName3,

etc...

FROM

(SELECT

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,

SkillTargetID,

SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

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,

SkillTargetID

) AED

LEFT JOIN Agent_Skill_Group_Half_Hour ASGHH

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN more tables

and so on...

Computing the Interval for AED is a little tricky... more information on why I did is as above here:

https://supportforums.cisco.com/discussion/12263946/ucce-cuic-interval-boundary-agent-not-ready

The calculation is for 30-minute intervals... if you use 15-minute intervals in your environment it will take a bit more work to get it set up for that.

You won't need any GROUP BY in the outer SELECT statement unless you're doing any further aggregation after joining with ASGHH or ASGI, for example if you were to make a version of the report definition that summarized by Day, Week, or Month.

-Jameson

Jameson,

Thank you so much for the code example, I really appreciate it!

I didn´t succeed to make it work though, see my query below.

I´m getting the following error message:

Create the parameters or correct the query syntax and recreate the parameters. No column was specified for column 3 of 'AED'.

SELECT

aed.Duration as NotReadyDuration,

(ASGHH.CallsHandled * 1.0) as CallsHandled

FROM

(SELECT

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,

SkillTargetID,

SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

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,

SkillTargetID

) AED

LEFT JOIN Agent_Skill_Group_Half_Hour ASGHH

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

Robert,

Try this:

SELECT

AED.Duration as NotReadyDuration,

ASGHH.CallsHandled,

ASGHH.SkillTargetID

FROM

(SELECT

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,

SkillTargetID,

Duration=SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

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,

SkillTargetID

) AED

LEFT JOIN Agent_Skill_Group_Half_Hour ASGHH

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

Looks like I forgot to name the SUM(Duration) statement.

-Jameson

That made the trick! I'm now able to get some data!

It looks a bit strange though - it does not get nearly as close as many calls handled in this report as on any stock agent report. I assumed there must be something wrong with the intervals so I changed the query to 15 instead of 30 mins.

I changed the table where I get the calls handled and so on.. maybe it has to do with this?

Any ideas?

SELECT

Agent.EnterpriseName,

AED.Duration as NotReadyDuration,

SUM(ISNULL(ASGHH.CallsHandled, 0)) AS CallsHandled,

SUM((ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + AED.Duration) / ASGHH.CallsHandled) as ACW,

SUM((ASGHH.TalkInTime + ASGHH.HoldTime + ASGHH.ReservedStateTime) / ASGHH.CallsHandled) as ATT,

SUM((ASGHH.TalkInTime + ASGHH.HoldTime + ASGHH.ReservedStateTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + AED.Duration) / ASGHH.CallsHandled) as AHT,

ASGHH.LoggedOnTime as LoggedOnTime,

PerReady = ISNULL(ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime * 1.0 / ASGHH.LoggedOnTime, 0),

TimeReady = ISNULL(ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime * 1.0, 0)

FROM

(SELECT

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

SkillTargetID,

Duration=SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

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

SkillTargetID

) AED

LEFT JOIN Agent_Skill_Group_Interval ASGHH

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGHH.SkillTargetID

GROUP BY

AED.Duration, Agent.EnterpriseName, ASGHH.CallsHandled, ASGHH.HandledCallsTime, ASGHH.LoggedOnTime, ASGHH.BusyOtherTime, ASGHH.WorkNotReadyTime, ASGHH.WorkReadyTime, ASGHH.ReservedStateTime, ASGHH.AvailTime, ASGHH.TalkInTime,  ASGHH.HoldTime

Output example ( I will group it with summary only later, don\t need any details per interval)

Robert,

Your Interval evaluation for 15 minutes is not quite right, try this instead:

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

Also, we should probably change the first join to an OUTER JOIN, as LEFT JOIN will only keep rows where your AED query has values. You'll have to add this to the outer query as well, otherwise there will be too much data after the JOIN change:

WHERE  ASGHH.SkillTargetID IN (:AgentSkillTargetID)

and ASGHH.DateTime >= :startDate

and ASGHH.DateTime <= :endDate

Your AHT, ATT, and ACW calculations are not going to work properly... I would rethink how you build them. Keep this in mind:

(A1/B1)+(A2/B2) IS NOT EQUAL TO (A1+A2)/(B1+B2)

You should SUM before division. I usually just save calculations like this for Formula Fields... it keeps the SQL query simpler, and makes it easier to build the custom footer for these fields (Never use a Sum or an Average footer for AHT and such).

-Jameson

Jameson,

It seems like it still does not fetch enough data from the _ table.

Also I couldnt get the "WHERE" into the outer join, error message.

Thanks for the hint about the calculations - I´ll focus on this as soon as I know the query gets all the data

Here´s the query after the latest changes.

SELECT

Agent_Team.EnterpriseName as TeamName,

(Person.FirstName + ' ' + Person.LastName) as FullName,

SUM(ISNULL(ASGHH.CallsHandled, 0)) AS CallsHandled,

SUM((ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + AED.Duration) / ASGHH.CallsHandled) as ACW,

SUM((ASGHH.TalkInTime + ASGHH.HoldTime + ASGHH.ReservedStateTime) / ASGHH.CallsHandled) as ATT,

SUM((ASGHH.TalkInTime + ASGHH.HoldTime + ASGHH.ReservedStateTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + AED.Duration) / ASGHH.CallsHandled) as AHT,

ASGHH.LoggedOnTime as LoggedOnTime,

PerReady = ISNULL(ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime * 1.0 / ASGHH.LoggedOnTime, 0),

TimeReady = ISNULL(ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime * 1.0, 0),

PerNotReady =  ASGHH.LoggedOnTime - (ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime) / ASGHH.LoggedOnTime,

NotReadyTime = ASGHH.LoggedOnTime - (ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime)

FROM (SELECT

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

SkillTargetID,

Duration=SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

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

SkillTargetID

) AED

LEFT OUTER JOIN Agent_Skill_Group_Interval ASGHH

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Person

ON Person.PersonID=Agent.PersonID

LEFT JOIN Agent_Team_Member

ON Agent_Team_Member.SkillTargetID=Agent.SkillTargetID

LEFT JOIN Agent_Team

ON Agent_Team.AgentTeamID=Agent_Team_Member.AgentTeamID

GROUP BY

AED.Duration, Agent.EnterpriseName, ASGHH.CallsHandled, ASGHH.HandledCallsTime, ASGHH.LoggedOnTime, ASGHH.BusyOtherTime, ASGHH.WorkNotReadyTime, ASGHH.WorkReadyTime, ASGHH.ReservedStateTime, ASGHH.AvailTime, ASGHH.TalkInTime,  ASGHH.HoldTime, ASGHH.NotReadyTime, Person.FirstName, Person.LastName, Agent_Team.EnterpriseName, Agent_Team_Member.AgentTeamID

Hmm, I think I was looking at that JOIN wrong. You should have a "RIGHT JOIN" instead of the "LEFT OUTER JOIN". That should take all appropriate rows from Agent_Skill_Group_Interval, and join them with any matching rows from the AED summary.

As an alternative, if you wanted to keep all of the JOINs as LEFT JOIN (probably an easier design to read), you can do this:

SELECT

Agent_Team.EnterpriseName as TeamName,

(Person.FirstName + ' ' + Person.LastName) as FullName,

SUM(ISNULL(ASGHH.CallsHandled, 0)) AS CallsHandled,

SUM((ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + AED.Duration) / ASGHH.CallsHandled) as ACW,

SUM((ASGHH.TalkInTime + ASGHH.HoldTime + ASGHH.ReservedStateTime) / ASGHH.CallsHandled) as ATT,

SUM((ASGHH.TalkInTime + ASGHH.HoldTime + ASGHH.ReservedStateTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + AED.Duration) / ASGHH.CallsHandled) as AHT,

ASGHH.LoggedOnTime as LoggedOnTime,

PerReady = ISNULL(ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime * 1.0 / ASGHH.LoggedOnTime, 0),

TimeReady = ISNULL(ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime * 1.0, 0),

PerNotReady =  ASGHH.LoggedOnTime - (ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime) / ASGHH.LoggedOnTime,

NotReadyTime = ASGHH.LoggedOnTime - (ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime)

 

FROM Agent_Skill_Group_Interval ASGHH

LEFT JOIN (SELECT

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

SkillTargetID,

Duration=SUM(Duration)

 

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

 

GROUP BY

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

SkillTargetID

) AED

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Person

ON Person.PersonID=Agent.PersonID

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  ASGHH.SkillTargetID IN (:AgentSkillTargetID)

and ASGHH.DateTime >= :startDate

and ASGHH.DateTime <= :endDate

 

 

GROUP BY

AED.Duration, Agent.EnterpriseName, ASGHH.CallsHandled, ASGHH.HandledCallsTime, ASGHH.LoggedOnTime, ASGHH.BusyOtherTime, ASGHH.WorkNotReadyTime, ASGHH.WorkReadyTime, ASGHH.ReservedStateTime, ASGHH.AvailTime, ASGHH.TalkInTime,  ASGHH.HoldTime, ASGHH.NotReadyTime, Person.FirstName, Person.LastName, Agent_Team.EnterpriseName, Agent_Team_Member.AgentTeamID

- Jameson

Jameson,

This made the trick, it works!

Thank you so much for your help, I really appreciate it!

Have a great day!

Cheers

Jameson,

Sorry, but found another issue with the login times, it seems like it adds multiple times because it´s way too high figures.

See my screenshot below, the actual login time should be 03:40:00 on this user. The ready and not ready time has the same issue.

Any idea?

Robert,

I suggest you expose ASGHH.DateTime and the SkillGroupID until you actually start summarizing things. It looks like you've got data for multiple Skill Groups for each DateTime value.

If you want accurate LoggedOnTime and NotReadyTime, I would bring in the Agent_Interval table... perhaps summarizing it separately like we did the AED table.

-Jameson

Hey Jameson,

Thanks for the quick reply!

Yeah you´re correct, there are several records with different SkillGroupSkillTargetID !

See the screenshot below, it shows several identical timestamps with different Skillgroup id´s

I tried joining the Agent_Interval table separately like the AED table, but see the column LoggedOnTimeTEST - it shows the exact same value as LoggedOnTime.. So the error in this must be that there are duplicates because of several skill group id´s?

Any tips?

Here´s the query:

SELECT

Agent_Team.EnterpriseName as TeamName,

CONVERT(char(10),ASGHH.DateTime,101) as Date,

CONVERT(char(5), ASGHH.DateTime, 108) as Time,

ASGHH.SkillTargetID,

ASGHH.SkillGroupSkillTargetID,

AI.LoggedOnTimeTEST,

(Person.FirstName + ' ' + Person.LastName) as FullName,

SUM(ISNULL(ASGHH.CallsHandled, 0)) AS CallsHandled,

SUM((ISNULL(ASGHH.WorkNotReadyTime, 0) + ISNULL(ASGHH.WorkReadyTime, 0) + ISNULL(AED.Duration, 0)) / ISNULL(ASGHH.CallsHandled, 0)) as ACW,

SUM((ASGHH.TalkInTime + ASGHH.HoldTime + ASGHH.ReservedStateTime) / ASGHH.CallsHandled) as ATT,

SUM((ISNULL(ASGHH.TalkInTime, 0) + ISNULL(ASGHH.HoldTime, 0) + ISNULL(ASGHH.ReservedStateTime, 0) + ISNULL(ASGHH.WorkNotReadyTime, 0) + ISNULL(ASGHH.WorkReadyTime, 0) + ISNULL(AED.Duration, 0)) / ISNULL(ASGHH.CallsHandled, 0)) as AHT,

ASGHH.LoggedOnTime as LoggedOnTime,

SUM(ASGHH.LoggedOnTime) as LoggedOnTimeTEST,

PerReady = ISNULL(ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime * 1.0 / ASGHH.LoggedOnTime, 0),

TimeReady = ISNULL(ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime * 1.0, 0),

PerNotReady =  ASGHH.LoggedOnTime - (ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime) / ASGHH.LoggedOnTime,

NotReadyTime = ASGHH.LoggedOnTime - (ASGHH.BusyOtherTime + ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime + ASGHH.ReservedStateTime + ASGHH.AvailTime + ASGHH.TalkInTime +  ASGHH.HoldTime)

FROM Agent_Skill_Group_Interval ASGHH

LEFT OUTER JOIN (SELECT

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

SkillTargetID,

Duration=SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

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

SkillTargetID

) AED

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

LEFT OUTER JOIN Agent

ON Agent.SkillTargetID=ASGHH.SkillTargetID

LEFT OUTER JOIN Person

ON Person.PersonID=Agent.PersonID

LEFT OUTER JOIN Agent_Team_Member

ON Agent_Team_Member.SkillTargetID=Agent.SkillTargetID

LEFT OUTER JOIN Agent_Team

ON Agent_Team.AgentTeamID=Agent_Team_Member.AgentTeamID

LEFT OUTER JOIN (SELECT

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

    SkillTargetID,

    LoggedOnTimeTEST=SUM(LoggedOnTime)

    FROM Agent_Interval

    WHERE  SkillTargetID IN (:AgentSkillTargetID)

    and DateTime >= :startDate

    and DateTime <= :endDate

    GROUP BY

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

    SkillTargetID

    ) AI

ON AI.Interval=ASGHH.DateTime AND AI.SkillTargetID=ASGHH.SkillTargetID

WHERE  ASGHH.SkillTargetID IN (:AgentSkillTargetID)

and ASGHH.DateTime >= :startDate

and ASGHH.DateTime <= :endDate

GROUP BY

AED.Duration, Agent.EnterpriseName, ASGHH.CallsHandled, ASGHH.HandledCallsTime, ASGHH.LoggedOnTime, ASGHH.BusyOtherTime, ASGHH.WorkNotReadyTime, ASGHH.WorkReadyTime, ASGHH.ReservedStateTime, ASGHH.AvailTime, ASGHH.TalkInTime,  ASGHH.HoldTime, ASGHH.NotReadyTime, Person.FirstName, Person.LastName, Agent_Team.EnterpriseName, Agent_Team_Member.AgentTeamID ,ASGHH.DateTime, ASGHH.SkillTargetID, AI.LoggedOnTimeTEST, AI.SkillTargetID, ASGHH.SkillGroupSkillTargetID

Robert,

Do you plan on dividing data by Skill Group in the final report? If not, I would do the following:

• Remove ASGHH.LoggedOnTime from SELECT and GROUP BY (use AI.NotReadyTime instead)
• Remove from GROUP BY anything that is only in a SUM statement (CallsHandled, etc)
• Remove all references to SkillGroupSkillTargetID, ASGHH.NotReadyTime (use AI.NotReadyTime instead)
• Any data that's from ASGHH needs to be in a SUM statement, and definitely not in GROUP BY

-Jameson

Jameson,

Thanks - I finally figured out when to group and when not to!

I put everything that needs to be inside a SUM statement, and removed some calculations for now. (Will add them properly later as soon as I have the LoggedOnTime working)

Also I removed what you said, won´t need to sum by skill group for now! Maybe later if I get everything to work.

I´m not getting correct values yet though - see LoggedOnTime and NotReadyTime below, seems like theyre multiplied with 6? Some other agents were multiplied with other values (like 3, 4...)

Here´s my current query:

SELECT

(Person.FirstName + ' ' + Person.LastName) as FullName,

Agent_Team.EnterpriseName as TeamName,

CONVERT(char(10),ASGHH.DateTime,101) as Date,

CONVERT(char(5), ASGHH.DateTime, 108) as Time,

SUM(ISNULL(AI.LoggedOnTime, 0)) AS LoggedOnTime,

SUM(AI.NotReadyTime) as NotReadyTime,

SUM(ISNULL(ASGHH.CallsHandled, 0)) AS CallsHandled,

SUM((ISNULL(ASGHH.WorkNotReadyTime, 0) + ISNULL(ASGHH.WorkReadyTime, 0) + ISNULL(AED.Duration, 0)) / ISNULL(ASGHH.CallsHandled, 0)) as ACW,

SUM((ASGHH.TalkInTime + ASGHH.HoldTime + ASGHH.ReservedStateTime) / ASGHH.CallsHandled) as ATT,

SUM((ISNULL(ASGHH.TalkInTime, 0) + ISNULL(ASGHH.HoldTime, 0) + ISNULL(ASGHH.ReservedStateTime, 0) + ISNULL(ASGHH.WorkNotReadyTime, 0) + ISNULL(ASGHH.WorkReadyTime, 0) + ISNULL(AED.Duration, 0)) / ISNULL(ASGHH.CallsHandled, 0)) as AHT

FROM Agent_Skill_Group_Interval ASGHH

LEFT OUTER JOIN (SELECT

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

SkillTargetID,

Duration=SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

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

SkillTargetID

) AED

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Person

ON Person.PersonID=Agent.PersonID

LEFT JOIN Agent_Team_Member

ON Agent_Team_Member.SkillTargetID=Agent.SkillTargetID

LEFT JOIN Agent_Team

ON Agent_Team.AgentTeamID=Agent_Team_Member.AgentTeamID

LEFT OUTER JOIN (SELECT

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

    SkillTargetID,

    LoggedOnTime=SUM(LoggedOnTime),

    NotReadyTime=SUM(NotReadyTime)

    FROM Agent_Interval

    WHERE  SkillTargetID IN (:AgentSkillTargetID)

    and DateTime >= :startDate

    and DateTime <= :endDate

    GROUP BY

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

    SkillTargetID

    ) AI

ON AI.Interval=ASGHH.DateTime AND AI.SkillTargetID=ASGHH.SkillTargetID

WHERE  ASGHH.SkillTargetID IN (:AgentSkillTargetID)

and ASGHH.DateTime >= :startDate

and ASGHH.DateTime <= :endDate

GROUP BY

Agent_Team.EnterpriseName, ASGHH.DateTime, ASGHH.SkillTargetID, Person.FirstName, Person.LastName

Hi Robert,

This is an issue I came up against a while back. The problem your encountering is due to the fact that even without referencing specific skill groups, the rows are still there and by joining Agent_Interval to Agent_Skill_Group_Interval, you are joining to every Agent and Interval instance that has a different skill group in Agent_Skill_Group_Interval. Jameson was on the right track in removing references to skill group, but rather than separating out Agent_Interval like you did with AED, you need to separate out Agent_Skill_Group_Interval and group it by Agent and DateTime so you can get rid of the Skill Group.

I would do it like this:

(Select DateTime,

SkillTargetID,

CallsHandled = SUM(ISNULL(CallsHandled,0)),

WorkNotReadyTime = SUM(ISNULL(WorkNotReadyTime,0)),

WorkReadyTime = SUM(ISNULL(WorkReadyTime,0)),

TalkInTime = SUM(ISNULL(TalkInTime,0)),

HoldTime = SUM(ISNULL(HoldTime,0)),

ReservedStateTime = SUM(ISNULL(ReservedStateTime,0))

FROM Agent_Skill_Group_Interval

WHERE SkillTargetID IN (:AgentSkillTargetID)

    and DateTime >= :startDate

    and DateTime <= :endDate

GROUP BY DateTime, SkillTargetID) ASGHH

Keep in mind, you'll still have to call the fields in the initial select statement above and do your equations there (division doesn't do well with this) and if you want to add anything you'll have to do it in here and then in the first select statement.

- Tappan

Hi Richard,

Thanks for your reply and the great explanation!

I´m not sure how to implement your code into my previously posted query, just can´t get it to work.

Can you help me with pasting it to the right place? And I´ll try to call the rest of the fields myself

Thanks again! I appreciate the awesome help you both has given me!

Hi Robert,

No problem! Glad this makes sense.

Paste it in the initial FROM statement where it says "FROM Agent_Skill_Group_Interval ASGHH" and replace "Agent_Skill_Group_Interval ASGHH" with this code. Then just try running it and it should work. If you're still having issues paste your code and we can work out the problem.

-Tappan

Thanks man, got it running!

But it´s still showing the same logged in time as before, (01:15:00) see my query below, just added a few columns to the top to check.

SELECT

(Person.FirstName + ' ' + Person.LastName) as FullName,

Agent_Team.EnterpriseName as TeamName,

CONVERT(char(10),ASGHH.DateTime,101) as Date,

CONVERT(char(5), ASGHH.DateTime, 108) as Time,

SUM(ISNULL(ASGHH.LoggedOnTime,0)) as LoggedOnTime,

SUM(ISNULL(ASGHH.CallsHandled,0)) as CallsHandled,

SUM(AED.Duration) as NotReadySeventyone

FROM

(Select DateTime,

SkillTargetID,

LoggedOnTime= SUM(ISNULL(LoggedOnTime,0)),

CallsHandled = SUM(ISNULL(CallsHandled,0)),

WorkNotReadyTime = SUM(ISNULL(WorkNotReadyTime,0)),

WorkReadyTime = SUM(ISNULL(WorkReadyTime,0)),

TalkInTime = SUM(ISNULL(TalkInTime,0)),

HoldTime = SUM(ISNULL(HoldTime,0)),

ReservedStateTime = SUM(ISNULL(ReservedStateTime,0))

FROM Agent_Skill_Group_Interval

WHERE SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY DateTime, SkillTargetID) ASGHH

LEFT OUTER JOIN (SELECT

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

SkillTargetID,

Duration=SUM(Duration)

FROM Agent_Event_Detail

WHERE  SkillTargetID IN (:AgentSkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and ReasonCode = '71'

GROUP BY

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

SkillTargetID

) AED

ON AED.Interval=ASGHH.DateTime AND AED.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGHH.SkillTargetID

LEFT JOIN Person

ON Person.PersonID=Agent.PersonID

LEFT JOIN Agent_Team_Member

ON Agent_Team_Member.SkillTargetID=Agent.SkillTargetID

LEFT JOIN Agent_Team

ON Agent_Team.AgentTeamID=Agent_Team_Member.AgentTeamID

LEFT OUTER JOIN (SELECT

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

    SkillTargetID,

    LoggedOnTime=SUM(LoggedOnTime),

    NotReadyTime=SUM(NotReadyTime)

    FROM Agent_Interval

    WHERE  SkillTargetID IN (:AgentSkillTargetID)

    and DateTime >= :startDate

    and DateTime <= :endDate

    GROUP BY

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

    SkillTargetID

    ) AI

ON AI.Interval=ASGHH.DateTime AND AI.SkillTargetID=ASGHH.SkillTargetID

WHERE  ASGHH.SkillTargetID IN (:AgentSkillTargetID)

and ASGHH.DateTime >= :startDate

and ASGHH.DateTime <= :endDate

GROUP BY

Agent_Team.EnterpriseName, ASGHH.DateTime, ASGHH.SkillTargetID, Person.FirstName, Person.LastName

Hey Robert,

You're welcome! You're seeing this because you're still using Agent_Skill_Group_Interval's logged on time, which sums across skill groups. So you're seeing the logged on time for that agent for each skill group they are in. For logged on time and not ready time you should still use Agent_Interval (AI). The code I gave you allows you to join to that so you don't see odd values.

-Tappan

Hi Richard,

That made the trick! Thank you very much for helping me out.

Everything looks 100% correct now so I can now finish the report. I´m happy!

Thanks again guys for your awesome help!