Answered/handled distribution - Need help with too many rows

[roberteliasson]

Hi,

I need some assistance with the script below, it returns multiple rows for each Agent.

I assume it has to do with the DateTime statement, but I can´t come up with a solution to remove it. (See the bold red text below)

Anyone got any idea on how I can reduce this to 1 row per Agent?

Thanks in advance!

SELECT

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

Agent_Team.EnterpriseName as TeamName,

SUM(ASGI.CallsAnswered) as CallsAnswered,

SUM(ASGI.CallsHandled) as CallsHandled

FROM

(Select DateTime,

SkillTargetID,

CallsAnswered = SUM(ISNULL(CallsAnswered,0)),

CallsHandled = SUM(ISNULL(CallsHandled,0))

FROM Agent_Skill_Group_Interval

WHERE SkillTargetID IN (:SkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY DateTime, SkillTargetID) ASGI

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGI.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  ASGI.SkillTargetID IN (:SkillTargetID)

and ASGI.DateTime >= :startDate

and ASGI.DateTime <= :endDate

GROUP BY

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

[Jameson Gagnepain]

Robert,

Remove "ASGI.DateTime" from the final GROUP BY statement, and you should be down to one line per agent.

In fact, I'd probably re-write the whole thing as a regular query instead of an anonymous block... your sub-query looks unnecessary to me. This is a simpler query, and gives you the benefit of better filter selection:

SELECT

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

Agent_Team.EnterpriseName as TeamName,

SUM(ASGI.CallsAnswered) as CallsAnswered,

SUM(ASGI.CallsHandled) as CallsHandled

FROM Agent_Skill_Group_Interval ASGI

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGI.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

Agent_Team.EnterpriseName, ASGI.SkillTargetID, Person.FirstName, Person.LastName

In the Fields tab of the report definition, you can create Filter Fields for ASGI.DateTime and ASGI.SkillTargetID. CUIC will automatically do the "WHERE" portion of the query from those.

-Jameson

[roberteliasson]

Thanks a bunch Jameson, that made the trick!

I´ve continued with the query now, with adding the figures 'calls coded' a.k.a. number of wrap up codes. This is the reason why I´m creating this script - to see the split between answered calls and coded calls.

For some reason I´m not getting all coded calls though.. It doesn´t show the correct figure. Do you have any idea why?

SELECT

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

Agent_Team.EnterpriseName as TeamName,

SUM(ISNULL(ASGI.CallsAnswered,0)) as CallsAnswered,

SUM(ISNULL(ASGI.CallsAnswered,0)) as PerHandled,

COUNT(TCD.CallsCoded) as CallsCoded

FROM

(Select DateTime,

SkillTargetID,

CallsAnswered = SUM(ISNULL(CallsAnswered,0)),

CallsHandled = SUM(ISNULL(CallsHandled,0))

FROM Agent_Skill_Group_Interval

WHERE SkillTargetID IN (:SkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY DateTime, SkillTargetID) ASGI

LEFT OUTER JOIN (SELECT

AgentSkillTargetID,

CallsCoded = COUNT(WrapupData)

FROM Termination_Call_Detail

WHERE  AgentSkillTargetID IN (:SkillTargetID)

and WrapupData is not null

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY AgentSkillTargetID

) TCD

ON TCD.AgentSkillTargetID=ASGI.SkillTargetID

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGI.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  ASGI.SkillTargetID IN (:SkillTargetID)

and ASGI.DateTime >= :startDate

and ASGI.DateTime <= :endDate

GROUP BY

Agent_Team.EnterpriseName, ASGI.SkillTargetID, Person.FirstName, Person.LastName

[Jameson Gagnepain · Accepted solution]

One error I see: "COUNT(TCD.CallsCoded) as CallsCoded" in your first SELECT statement should be a SUM instead. Counting something that's already summarized is likely to give you a value of 1 for CallsCoded for each agent.

A couple things to note on DateTime values... you'll want to keep these in mind:

• All DateTime values in Agent_Skill_Group_Interval are for the interval as a whole... So if you have 30-minute intervals in your system, a row for 10:30AM has data from 10:30:00AM to 10:59:59AM. Statistics like CallsAnswered, CallsHandled, etc. are incremented in the interval they occurred in, so you can have a call Answered in one interval, and Handled in the next.
• All DateTime values in Termination_Call_Detail are for when the call leg completed.

So, if your DateTime filter criteria is something like October 21, 2014, 10:30 AM to 11:00AM, you'll see Agent Skill Group data for calls that were answered between 10:30:00 and 11:29:59, calls that were handled (completed wrapup) in that same interval, and you'll see TCD data for calls that completed between 10:30:00 and 11:00:00.

It may be possible to change the TCD part of your query to automatically handle the Interval-DateTime differences.

-Jameson

[roberteliasson]

Jameson,

Thank you so much for your explanations! However - I will only run this report on a monthly basis between 01:00AM and 12:00PM, so it shouldn´t be a problem - right?

I changed the COUNT to SUM but now it gives me a really high value instead, see the screenshot below.

The proper value of the 2nd row which shows 18 and 588 should be 31 (when looking at the Supervisor Desktop log)

Here´s the script:

SELECT

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

Agent_Team.EnterpriseName as TeamName,

SUM(ISNULL(ASGI.CallsAnswered,0)) as CallsAnswered,

SUM(ISNULL(ASGI.CallsAnswered,0)) as PerHandled,

COUNT(TCD.CallsCoded) as CallsCoded,

SUM(TCD.CallsCoded) as CallsCodednew

FROM

(Select DateTime,

SkillTargetID,

CallsAnswered = SUM(ISNULL(CallsAnswered,0)),

CallsHandled = SUM(ISNULL(CallsHandled,0))

FROM Agent_Skill_Group_Interval

WHERE SkillTargetID IN (:SkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY DateTime, SkillTargetID) ASGI

LEFT OUTER JOIN (SELECT

AgentSkillTargetID,

CallsCoded = COUNT(WrapupData)

FROM Termination_Call_Detail

WHERE  AgentSkillTargetID IN (:SkillTargetID)

and WrapupData is not null

and DateTime >= :startDate

and DateTime <= :endDate

GROUP BY AgentSkillTargetID

) TCD

ON TCD.AgentSkillTargetID=ASGI.SkillTargetID

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGI.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  ASGI.SkillTargetID IN (:SkillTargetID)

and ASGI.DateTime >= :startDate

and ASGI.DateTime <= :endDate

GROUP BY

Agent_Team.EnterpriseName, ASGI.SkillTargetID, Person.FirstName, Person.LastName

[roberteliasson]

FYI I just solved it, here´s the query in case anyone needs something similar.

Thanks for the help Jameson!

SELECT

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

Agent_Team.EnterpriseName as TeamName,

SUM(ISNULL(ASGI.CallsAnswered,0)) as CallsAnswered,

SUM(ISNULL(TCD.CallsCoded,0)/ISNULL(ASGI.CallsAnswered,0)) as PerHandled,

SUM(ISNULL(TCD.CallsCoded,0)) as CallsCoded,

SUM(ISNULL(ASGI.CallsAnswered,0)-ISNULL(TCD.CallsCoded,0)) as CallsNotCoded

FROM

(Select DateTime,

SkillTargetID,

CallsAnswered = SUM(ISNULL(CallsAnswered,0)),

CallsHandled = SUM(ISNULL(CallsHandled,0))

FROM Agent_Skill_Group_Interval

WHERE SkillTargetID IN (:SkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and SkillGroupSkillTargetID IN (:SkillGroup)

GROUP BY DateTime, SkillTargetID) ASGI

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,

AgentSkillTargetID,

CallsCoded = COUNT(ISNULL(WrapupData,0))

FROM Termination_Call_Detail

WHERE  AgentSkillTargetID IN (:SkillTargetID)

and WrapupData is not null

and DateTime >= :startDate

and DateTime <= :endDate

and SkillGroupSkillTargetID IN (:SkillGroup)

GROUP BY AgentSkillTargetID,

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

) TCD

ON TCD.AgentSkillTargetID=ASGI.SkillTargetID AND TCD.Interval=ASGI.DateTime

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGI.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  ASGI.SkillTargetID IN (:SkillTargetID)

and ASGI.DateTime >= :startDate

and ASGI.DateTime <= :endDate

GROUP BY

Agent_Team.EnterpriseName, ASGI.SkillTargetID, Person.FirstName, Person.LastName, TCD.AgentSkillTargetID

[Jameson Gagnepain]

That looks good, Robert. Glad to see it's working.  In the interest of further optimization...

One alternative solution would be to remove all DateTime grouping and selection from each of your sub-queries. Looking at your earlier query that has both the Count and Sum columns, I see that the Count value is actually the number of different DateTimes from the ASGI sub-query. If you did SUM(CallsCoded)/COUNT(CallsCoded) in that earlier query, you would see the same result as your last query.

Removing all DateTime references (aside from the WHERE statements) would make the SQL a little easier to read, and a little faster to run. Here's what I mean:

SELECT

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

Agent_Team.EnterpriseName as TeamName,

SUM(ISNULL(ASGI.CallsAnswered,0)) as CallsAnswered,

SUM(ISNULL(TCD.CallsCoded,0)/ISNULL(ASGI.CallsAnswered,0)) as PerHandled,

SUM(ISNULL(TCD.CallsCoded,0)) as CallsCoded,

SUM(ISNULL(ASGI.CallsAnswered,0)-ISNULL(TCD.CallsCoded,0)) as CallsNotCoded

FROM

(Select

SkillTargetID,

CallsAnswered = SUM(ISNULL(CallsAnswered,0)),

CallsHandled = SUM(ISNULL(CallsHandled,0))

FROM Agent_Skill_Group_Interval

WHERE SkillTargetID IN (:SkillTargetID)

and DateTime >= :startDate

and DateTime <= :endDate

and SkillGroupSkillTargetID IN (:SkillGroup)

GROUP BY SkillTargetID) ASGI

LEFT OUTER JOIN (SELECT

AgentSkillTargetID,

CallsCoded = COUNT(ISNULL(WrapupData,0))

FROM Termination_Call_Detail

WHERE  AgentSkillTargetID IN (:SkillTargetID)

and WrapupData is not null

and DateTime >= :startDate

and DateTime <= :endDate

and SkillGroupSkillTargetID IN (:SkillGroup)

GROUP BY AgentSkillTargetID

) TCD

ON TCD.AgentSkillTargetID=ASGI.SkillTargetID

LEFT JOIN Agent

ON Agent.SkillTargetID=ASGI.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

Agent_Team.EnterpriseName, ASGI.SkillTargetID, Person.FirstName, Person.LastName, TCD.AgentSkillTargetID

I also removed your final WHERE statement, it should be unnecessary as you're already filtering on the SkillTargetID and DateTime in your sub-queries.

-Jameson

[roberteliasson]

Indeed Jameson, that looks so much better! I´ll do the changes according to your tips and trix.

Thank you so much!!