10-21-2014 06:10 AM
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
Solved! Go to Solution.
10-21-2014 09:18 AM
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:
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
10-21-2014 06:34 AM
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
10-21-2014 08:22 AM
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
10-21-2014 09:18 AM
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:
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
10-21-2014 11:17 PM
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
10-22-2014 05:37 AM
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
10-22-2014 07:31 AM
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
10-23-2014 12:11 AM
Indeed Jameson, that looks so much better! I´ll do the changes according to your tips and trix.
Thank you so much!!
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide