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

[roberteliasson]

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

[Jameson Gagnepain · Accepted solution]

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

[roberteliasson]

Jameson,

This made the trick, it works!

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

Have a great day!

Cheers

[roberteliasson]

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?

[Jameson Gagnepain]

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

[roberteliasson]

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

[Jameson Gagnepain]

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

[roberteliasson]

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

[richapar]

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

[roberteliasson]

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!

[richapar]

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

[roberteliasson]

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

[richapar]

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

[roberteliasson]

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!