Hey,
I would like to combine metrics gathered by both SGs and SGs.
CallsOffered (from any table) and AHT,TalkTime,ReservedStateTime and so on.. (preferrable from the Agent_Skill_Group_Interval table)
But I really can´t find any ID i can combine them between (like SkillTargetID or CallTypeID).
Anyone who has done this before, or has any tips on how i can do it?
Robert,
What version of UCCE and CUIC are you on? Versions 8 and up have "Call Type Skill Group" reports already. Take a look at the Call_Type_Skill_Group_Interval table in the Database Schema.
-Jameson
Hi Jameson,
Version 9.1, Thank you for putting me in the right direction!
I added CallsOffered from another table (since CallsOfferedRouted column didnt show the correct values) but I´m getting really weird figures on this column. The correct total should be 22 inbound calls - but I´m getting WAY more than that in this report.
The datetime intervals should be exactly the same on both these tables, so I´m not sure what could be wrong.
Any ideas?
SELECT datetime= CTSG.DateTime,
calltypeId= CTSG.CallTypeID,
calltypeName= Call_Type.EnterpriseName,
answerWaitTime=sum(isnull(CTSG.AnswerWaitTime,0)),
CallsOffered=sum(isnull(Call_Type_Interval.CallsOffered,0)),
callsAnswered= sum(isnull( CTSG.CallsAnswered,0)),
callsHandled= sum(isnull(CTSG.CallsHandled,0)),
handleTime= sum(isnull(CTSG.HandleTime,0)),
talkTime= sum(isnull(CTSG.TalkTime,0)),
holdTime= sum(isnull(CTSG.HoldTime,0)),
callsOfferedRouted=sum(isnull(CTSG.CallsOfferedRouted,0)),
callsOfferedNotRouted=sum(isnull(CTSG.CallsOfferedNotRouted,0)),
overflowOut=sum(isnull(CTSG.OverflowOut,0)),
aat= sum(isnull(CTSG.TalkTime,0))/ sum(isnull(CTSG.CallsHandled,0)),
aht= sum(isnull(CTSG.HandleTime,0))/ sum(isnull(CTSG.CallsHandled,0)),
asa= sum(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0))
FROM Call_Type_SG_Interval CTSG (nolock), Call_Type (nolock), Skill_Group (nolock), Call_Type_Interval (nolock)
Where Skill_Group.SkillTargetID = CTSG.SkillGroupSkillTargetID
and Call_Type.CallTypeID = CTSG.CallTypeID
and Call_Type_Interval.CallTypeID = CTSG.CallTypeID
GROUP BY CTSG.CallTypeID, Call_Type.EnterpriseName, CTSG.DateTime
ORDER BY Call_Type.EnterpriseName, CTSG.DateTime
I would start by doing explicit joins instead of relying on SQL to create the joins for you... You also missed specifying CTSG.DateTime=Call_Type_Interval.DateTime. That specifically is why you've got much higher values than you expect - EVERY Call_Type_Interval row with matching CallTypeID is getting joined and summarized. Here's something for you to start with:
SELECT datetime= CTSG.DateTime, calltypeId= CTSG.CallTypeID,
calltypeName= Call_Type.EnterpriseName,
answerWaitTime=sum(isnull(CTSG.AnswerWaitTime,0)),
CallsOffered=sum(isnull(CTI.CallsOffered,0)),
callsAnswered= sum(isnull( CTSG.CallsAnswered,0)),
callsHandled= sum(isnull(CTSG.CallsHandled,0)),
handleTime= sum(isnull(CTSG.HandleTime,0)),
talkTime= sum(isnull(CTSG.TalkTime,0)),
holdTime= sum(isnull(CTSG.HoldTime,0)),
callsOfferedRouted=sum(isnull(CTSG.CallsOfferedRouted,0)),
callsOfferedNotRouted=sum(isnull(CTSG.CallsOfferedNotRouted,0)),
overflowOut=sum(isnull(CTSG.OverflowOut,0)),
aat= sum(isnull(CTSG.TalkTime,0))/ sum(isnull(CTSG.CallsHandled,0)),
aht= sum(isnull(CTSG.HandleTime,0))/ sum(isnull(CTSG.CallsHandled,0)),
asa= sum(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0))
FROM Call_Type_SG_Interval CTSG (nolock)
LEFT JOIN Call_Type CT (nolock)
ON CT.CallTypeID = CTSG.CallTypeID
LEFT JOIN Skill_Group SG (nolock)
ON SG.SkillTargetID = CTSG.SkillGroupSkillTargetID
LEFT JOIN Call_Type_Interval CTI (nolock)
ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime
GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime
ORDER BY CT.EnterpriseName, CTSG.DateTime
-Jameson
Thanks Jameson, that almost fixed it! However, there still is a difference between this new report and the standard reports.. And I have no clue why..
Tried adding DateTime from both tables just to see the intervals are the same - and they are.
Data from the new report:
Data from another (correct) report:
Bring in SG.EnterpriseName to your selection, Group By, and Order By. I think you'll find that many of the DateTime selections delivered calls to 5 different skill groups.
You are right, the calls are delivered to the different skill groups!
I have no use of seeing / summarizing inbound calls by SG though - is it possible to make it only SUM them for 1 Skill group for each Call Type? (Without having to add a parameter and choose the first skill group of each CT)
It's possible to do what you're looking for... it's a little complicated though. Here's a starting point for you:
SELECT
datetime= CTSG.DateTime,
calltypeId= CTSG.CallTypeID,
calltypeName= CT.EnterpriseName,
answerWaitTime=sum(CASE WHEN A.A=1 THEN isnull(CTSG.AnswerWaitTime,0) ELSE 0 END),
CallsOffered=sum(CASE WHEN A.A=2 THEN isnull(CTI.CallsOffered,0) ELSE 0 END),
callsAnswered= sum(CASE WHEN A.A=1 THEN isnull( CTSG.CallsAnswered,0) ELSE 0 END),
callsHandled= sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsHandled,0) ELSE 0 END),
handleTime= sum(CASE WHEN A.A=1 THEN isnull(CTSG.HandleTime,0) ELSE 0 END),
talkTime= sum(CASE WHEN A.A=1 THEN isnull(CTSG.TalkTime,0) ELSE 0 END),
holdTime= sum(CASE WHEN A.A=1 THEN isnull(CTSG.HoldTime,0) ELSE 0 END),
callsOfferedRouted=sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsOfferedRouted,0) ELSE 0 END),
callsOfferedNotRouted=sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsOfferedNotRouted,0) ELSE 0 END),
overflowOut=sum(CASE WHEN A.A=1 THEN isnull(CTSG.OverflowOut,0) ELSE 0 END),
aat= sum(CASE WHEN A.A=1 THEN isnull(CTSG.TalkTime,0) ELSE 0 END)/ NULLIF(sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsHandled,0) ELSE 0 END),0),
aht= sum(CASE WHEN A.A=1 THEN isnull(CTSG.HandleTime,0) ELSE 0 END)/ NULLIF(sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsHandled,0) ELSE 0 END),0),
asa= sum(CASE WHEN A.A=1 THEN isnull(CTSG.AnswerWaitTime,0) ELSE 0 END)/ NULLIF(sum(CASE WHEN A.A=1 THEN isnull(CTSG.CallsAnswered,0) ELSE 0 END),0)
FROM Call_Type_SG_Interval CTSG (nolock)
LEFT JOIN Call_Type CT (nolock)
ON CT.CallTypeID = CTSG.CallTypeID
LEFT JOIN Skill_Group SG (nolock)
ON SG.SkillTargetID = CTSG.SkillGroupSkillTargetID
LEFT JOIN Call_Type_Interval CTI (nolock)
ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime
LEFT JOIN (SELECT 1 AS A UNION SELECT 2 AS A) A
ON 1=1
GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime
ORDER BY CT.EnterpriseName, CTSG.DateTime
In a nutshell, I use the "A" table (which is just the values 1 and 2 in column A) to keep the data summarization from Call_Type_Interval separate from the data summarization for Call_Type_Skill_Group. If you add any more columns from Call_Type_Interval, make sure to enclose them in the same SUM(CASE) logic.
The other way to do this would be by summarizing CTSG and CTI in sub-queries and then joining them together... however, you would have to use an Anonymous Block instead of a standard SQL query in order to do DateTime selection.
-Jameson
Jameson,
Thank you very much!
The query above showed really weird results on every metric, so I decided to go for summarizing CTSG and CTI in sub-queries and it solved everything.
Thank you so much for your help, you saved the day (as always)
Here´s the query in case anyone needs it.
SELECT
CT.EnterpriseName as EnterpriseName,
CTSG.DateTime as DateTime,
SUM(CTI.CallsOffered) as CallsOffered,
SUM(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0)) as ASA,
SUM(isnull(CTSG.AnswerWaitTime,0)) as AnswerWaitTime,
SUM(isnull(CTSG.CallsAnswered,0)) as CallsAnswered
FROM
(Select
DateTime= DateTime,
CallTypeID= CallTypeID,
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
CallsAnswered= sum(isnull( CallsAnswered,0)),
callsHandled= sum(isnull(CallsHandled,0)),
handleTime= sum(isnull(HandleTime,0)),
talkTime= sum(isnull(TalkTime,0)),
holdTime= sum(isnull(HoldTime,0)),
callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),
callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),
overflowOut=sum(isnull(OverflowOut,0)),
aat= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),
aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),
asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0))
FROM Call_Type_SG_Interval
WHERE CallTypeID IN (:CallTypeID)
and DateTime >= :startDate
and DateTime <= :endDate
GROUP BY DateTime, CallTypeID) CTSG
LEFT JOIN Call_Type CT (nolock)
ON CT.CallTypeID = CTSG.CallTypeID
LEFT OUTER JOIN (SELECT
DateTime= DateTime,
CallTypeID,
CallsOffered=SUM(ISNULL(CallsOffered,0))
FROM Call_Type_Interval
WHERE CallTypeID IN (:CallTypeID)
and DateTime >= :startDate
and DateTime <= :endDate
GROUP BY CallTypeID, DateTime
) CTI
ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime
WHERE CTSG.CallTypeID IN (:CallTypeID)
AND CTSG.DateTime >= :startDate
AND CTSG.DateTime <= :endDate
GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime
ORDER BY CT.EnterpriseName, CTSG.DateTime
Just realized I have to add the column SkillGroupSkillTargetID from Call_Type_SG_Interval, since I will have to add the table Agent_Skill_Group_Interval to this report as well. (Need TalkInTime, TalkOutTime, AvailTime, HoldTime, BusyOtherTime, ReservedStateTime)
But as soon as I added SkillGroupSkillTargetID it took data from multiple Skill Groups again...
I did the following on the code below:
Removed SkillGroupSkillTargetID again from the first select
Added one more LEFT OUTER JOIN to the table Call_Type_SG_Interval and added the column 'SkillGroupSkillTargetID' here instead
But I still get CallsOffered from multiple Skill Groups..... Any idea?
SELECT
CT.EnterpriseName as EnterpriseName,
CTSGG.DateTime as DateTime,
SUM(CTI.CallsOffered) as CallsOffered,
SUM(isnull(CTSGG.AnswerWaitTime,0))/ sum(isnull(CTSGG.CallsAnswered,0)) as ASA,
SUM(isnull(CTSGG.AnswerWaitTime,0)) as AnswerWaitTime,
SUM(isnull(CTSGG.CallsAnswered,0)) as CallsAnswered
FROM
(Select
DateTime= DateTime,
CallTypeID= CallTypeID
FROM Call_Type_SG_Interval
WHERE CallTypeID IN (:CallTypeID)
and DateTime >= :startDate
and DateTime <= :endDate
GROUP BY DateTime, CallTypeID) CTSG
LEFT OUTER JOIN (SELECT
DateTime= DateTime,
CallTypeID= CallTypeID,
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
CallsAnswered= sum(isnull( CallsAnswered,0)),
callsHandled= sum(isnull(CallsHandled,0)),
handleTime= sum(isnull(HandleTime,0)),
talkTime= sum(isnull(TalkTime,0)),
holdTime= sum(isnull(HoldTime,0)),
callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),
callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),
overflowOut=sum(isnull(OverflowOut,0)),
att= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),
aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),
asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0)),
SkillGroupSkillTargetID
FROM Call_Type_SG_Interval
WHERE CallTypeID IN (:CallTypeID)
and DateTime >= :startDate
and DateTime <= :endDate
GROUP BY DateTime, CallTypeID, SkillGroupSkillTargetID
) CTSGG
ON CTSG.CallTypeID = CTSGG.CallTypeID AND CTSG.DateTime = CTSGG.DateTime
LEFT JOIN Call_Type CT (nolock)
ON CT.CallTypeID = CTSG.CallTypeID
LEFT OUTER JOIN (SELECT
DateTime= DateTime,
CallTypeID,
CallsOffered=SUM(ISNULL(CallsOffered,0)),
TalkTime = SUM(isnull(TalkTime,0)),
HoldTime = SUM(isnull(HoldTime,0))
FROM Call_Type_Interval
GROUP BY CallTypeID, DateTime
) CTI
ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime
LEFT OUTER JOIN (SELECT
SkillGroupSkillTargetID,
DateTime= DateTime,
TalkInTime = SUM(ISNULL(TalkInTime,0))
FROM Agent_Skill_Group_Interval
WHERE DateTime >= :startDate
and DateTime <= :endDate
GROUP BY SkillGroupSkillTargetID, DateTime
) ASGI
ON ASGI.SkillGroupSkillTargetID = CTSGG.SkillGroupSkillTargetID AND CTI.DateTime = CTSG.DateTime
WHERE CTSG.CallTypeID IN (:CallTypeID)
AND CTSG.DateTime >= :startDate
AND CTSG.DateTime <= :endDate
GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSGG.DateTime, CTSG.DateTime
ORDER BY CT.EnterpriseName, CTSG.DateTime
Hey Robert,
The reason you are seeing the Skill Group sum error show up again is you are grouping by SkillGroupSkillTargetID in the inner queries. By doing this you prevent the code from summing up the data from all skill groups into one row which creates issues with the join. What you can do instead is join Agent_Skill_Group_Interval inside the CTSGG table on SkillGroupSkillTargetID. It should look something like this:
(SELECT
DateTime= DateTime,
CallTypeID= CallTypeID,
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
CallsAnswered= sum(isnull( CallsAnswered,0)),
callsHandled= sum(isnull(CallsHandled,0)),
handleTime= sum(isnull(HandleTime,0)),
talkTime= sum(isnull(TalkTime,0)),
holdTime= sum(isnull(HoldTime,0)),
callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),
callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),
overflowOut=sum(isnull(OverflowOut,0)),
att= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),
aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),
asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0)),
TalkInTime = sum(isnull(ASGI.TalkInTime,0))
FROM Call_Type_SG_Interval
LEFT OUTER JOIN (SELECT
SkillGroupSkillTargetID,
DateTime= DateTime,
TalkInTime = SUM(ISNULL(TalkInTime,0))
FROM Agent_Skill_Group_Interval
WHERE DateTime >= :startDate
and DateTime <= :endDate
GROUP BY SkillGroupSkillTargetID, DateTime
) ASGI
ON ASGI.SkillGroupSkillTargetID = CTSGG.SkillGroupSkillTargetID AND CTI.DateTime = CTSG.DateTime
WHERE CallTypeID IN (:CallTypeID)
and DateTime >= :startDate
and DateTime <= :endDate
GROUP BY DateTime, CallTypeID
) CTSGG
This query also allows your to get rid of that CTSG table above since this table doesn't need to group by SkillGroupSkillTargetID anymore. Give that a try and let me know how it goes.
- Richard
Hey Richard,
Thanks for helping me out - I really appreciate it!
I tried your query but unfortunately it gave me the error below.
"Create the parameters or correct the query syntax and recreate the parameters. Incorrect syntax near 'CTSGG'."
Unfortunately I´m not too familiar with how you created this query so I have no idea how to fix it. I did some tries and ended up with the query below, but it gave me too many CallsOffered again.
SELECT
CTSG.DateTime,
SUM(ISNULL(CTI.CallsOffered,0)) as CallsOffered,
sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0)) as ASA
From (Select
DateTime= DateTime,
CallTypeID= CallTypeID,
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
CallsAnswered= sum(isnull( CallsAnswered,0)),
callsHandled= sum(isnull(CallsHandled,0)),
handleTime= sum(isnull(HandleTime,0)),
callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),
callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),
overflowOut=sum(isnull(OverflowOut,0)),
aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),
asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0)),
SkillGroupSkillTargetID
FROM Call_Type_SG_Interval
WHERE CallTypeID IN (:CallTypeID)
and DateTime >= :startDate
and DateTime <= :endDate
GROUP BY DateTime, CallTypeID, SkillGroupSkillTargetID) CTSG
LEFT OUTER JOIN (SELECT
SkillGroupSkillTargetID,
DateTime= DateTime,
TalkInTime = SUM(ISNULL(TalkInTime,0))
FROM Agent_Skill_Group_Interval
WHERE DateTime >= :startDate
and DateTime <= :endDate
GROUP BY SkillGroupSkillTargetID, DateTime
) ASGI
ON ASGI.SkillGroupSkillTargetID = CTSG.SkillGroupSkillTargetID AND ASGI.DateTime = CTSG.DateTime
LEFT OUTER JOIN (SELECT
DateTime= DateTime,
CallTypeID,
CallsOffered=SUM(ISNULL(CallsOffered,0)),
TalkTime = SUM(isnull(TalkTime,0)),
HoldTime = SUM(isnull(HoldTime,0))
FROM Call_Type_Interval
GROUP BY CallTypeID, DateTime
) CTI
ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime
WHERE CTSG.CallTypeID IN (:CallTypeID)
and CTSG.DateTime >= :startDate
and CTSG.DateTime <= :endDate
GROUP BY CTSG.DateTime
Hey Robert,
There were actually a couple of sloppy errors in the code I gave you. I had copy pasted without modifying a couple things so it would work. Try this out. I tested it and this does work. Should solve your problem.
SELECT
CT.EnterpriseName as EnterpriseName,
CTSG.DateTime as DateTime,
SUM(CTI.CallsOffered) as CallsOffered,
SUM(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0)) as ASA,
SUM(isnull(CTSG.AnswerWaitTime,0)) as AnswerWaitTime,
SUM(isnull(CTSG.CallsAnswered,0)) as CallsAnswered
FROM
(SELECT
DateTime= Call_Type_SG_Interval.DateTime,
CallTypeID= CallTypeID,
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
CallsAnswered= sum(isnull( CallsAnswered,0)),
callsHandled= sum(isnull(CallsHandled,0)),
handleTime= sum(isnull(HandleTime,0)),
talkTime= sum(isnull(TalkTime,0)),
holdTime= sum(isnull(HoldTime,0)),
callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),
callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),
overflowOut=sum(isnull(OverflowOut,0)),
att= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),
aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),
asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0)),
TalkInTime = sum(isnull(ASGI.TalkInTime,0))
FROM Call_Type_SG_Interval
LEFT OUTER JOIN (SELECT
SkillGroupSkillTargetID,
DateTime= DateTime,
TalkInTime = SUM(ISNULL(TalkInTime,0))
FROM Agent_Skill_Group_Interval
WHERE DateTime >= :startDate
and DateTime <= :endDate
GROUP BY SkillGroupSkillTargetID, DateTime
) ASGI
ON ASGI.SkillGroupSkillTargetID = Call_Type_SG_Interval.SkillGroupSkillTargetID AND ASGI.DateTime = Call_Type_SG_Interval.DateTime
WHERE CallTypeID IN (:CallTypeID)
and Call_Type_SG_Interval.DateTime >= :startDate
and Call_Type_SG_Interval.DateTime <= :endDate
GROUP BY Call_Type_SG_Interval.DateTime, CallTypeID
) CTSG
LEFT JOIN Call_Type CT (nolock)
ON CT.CallTypeID = CTSG.CallTypeID
LEFT OUTER JOIN (SELECT
DateTime= DateTime,
CallTypeID,
CallsOffered=SUM(ISNULL(CallsOffered,0)),
TalkTime = SUM(isnull(TalkTime,0)),
HoldTime = SUM(isnull(HoldTime,0))
FROM Call_Type_Interval
GROUP BY CallTypeID, DateTime
) CTI
ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime
WHERE CTSG.CallTypeID IN (:CallTypeID)
AND CTSG.DateTime >= :startDate
AND CTSG.DateTime <= :endDate
GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime, CTSG.DateTime
ORDER BY CT.EnterpriseName, CTSG.DateTime
Thanks man, it worked! Got the correct number of CallsOffered now
It doesnt seem like its working to add any columns from ASGI table to the top though, getting errors like this:
Create the parameters or correct the query syntax and recreate the parameters. The multi-part identifier "ASGI.BusyOtherTime" could not be bound.
Here´s my current query (will do the ACW, AHT, ATT calculations later)
SELECT
CT.EnterpriseName as EnterpriseName,
CTSG.DateTime as DateTime,
SUM(CTI.CallsOffered) as CallsOffered,
SUM(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0)) as ASA,
SUM(isnull(CTSG.AnswerWaitTime,0)) as AnswerWaitTime,
SUM(isnull(CTSG.CallsAnswered,0)) as CallsAnswered,
SUM(ISNULL(CTSG.CallsAnswered, 0)) as ACW,
SUM(ISNULL(CTSG.CallsAnswered, 0)) as ATT,
SUM(ISNULL(CTSG.CallsAnswered, 0)) as AHT,
SUM(ISNULL(ASGI.BusyOtherTime,0)) as BusyOtherTime,
SUM(ISNULL(ASGI.WorkReadyTime ,0)) as WorkReadyTime ,
SUM(ISNULL(ASGI.WorkNotReadyTime ,0)) as WorkNotReadyTime ,
SUM(ISNULL(ASGI.ReservedStateTime,0)) as ReservedStateTime,
--SUM(ISNULL(AI.AvailTime,0)) as AvailTime, --will add later
SUM(ISNULL(ASGI.TalkInTime,0) ) as TalkInTime,
SUM(ISNULL(ASGI.HoldTime,0)) as HoldTime
FROM
(SELECT
DateTime= Call_Type_SG_Interval.DateTime,
CallTypeID= CallTypeID,
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
CallsAnswered= sum(isnull( CallsAnswered,0)),
callsHandled= sum(isnull(CallsHandled,0)),
handleTime= sum(isnull(HandleTime,0)),
talkTime= sum(isnull(TalkTime,0)),
callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),
callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),
overflowOut=sum(isnull(OverflowOut,0)),
att= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),
aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),
asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0))
FROM Call_Type_SG_Interval
LEFT OUTER JOIN (SELECT
SkillGroupSkillTargetID,
DateTime= DateTime,
WorkNotReadyTime = SUM(ISNULL(WorkNotReadyTime,0)),
WorkReadyTime = SUM(ISNULL(WorkReadyTime,0)),
TalkInTime = SUM(ISNULL(TalkInTime,0)),
TalkOutTime= SUM(ISNULL(TalkOutTime,0)),
AgentOutCalls= SUM(ISNULL(AgentOutCalls,0)),
AvailTime= SUM(ISNULL(AvailTime,0)),
HoldTime = SUM(ISNULL(HoldTime,0)),
BusyOtherTime= SUM(ISNULL(BusyOtherTime,0)),
ReservedStateTime = SUM(ISNULL(ReservedStateTime,0))
FROM Agent_Skill_Group_Interval
WHERE DateTime >= :startDate
and DateTime <= :endDate
GROUP BY SkillGroupSkillTargetID, DateTime
) ASGI
ON ASGI.SkillGroupSkillTargetID = Call_Type_SG_Interval.SkillGroupSkillTargetID AND ASGI.DateTime = Call_Type_SG_Interval.DateTime
WHERE CallTypeID IN (:CallTypeID)
and Call_Type_SG_Interval.DateTime >= :startDate
and Call_Type_SG_Interval.DateTime <= :endDate
GROUP BY Call_Type_SG_Interval.DateTime, CallTypeID
) CTSG
LEFT JOIN Call_Type CT (nolock)
ON CT.CallTypeID = CTSG.CallTypeID
LEFT OUTER JOIN (SELECT
DateTime= DateTime,
CallTypeID,
CallsOffered=SUM(ISNULL(CallsOffered,0)),
TalkTime = SUM(isnull(TalkTime,0)),
HoldTime = SUM(isnull(HoldTime,0))
FROM Call_Type_Interval
GROUP BY CallTypeID, DateTime
) CTI
ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime
WHERE CTSG.CallTypeID IN (:CallTypeID)
AND CTSG.DateTime >= :startDate
AND CTSG.DateTime <= :endDate
GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime, CTSG.DateTime
ORDER BY CT.EnterpriseName, CTSG.DateTime
Hey Robert,
Because of the way this query is set up, adding fields from the ASGI table is a little trickier. Currently, you have a number of nested tables that you create inside the query and you are nesting the ASGI table inside the already nested CTSG table. So to add a field to the overall query, you need to add it first to the ASGI nested table, then call and sum that field in the CTSG table, then you can call that field in the top query as CTSG and the field name. Here's what the query will look like when you do that (and I've bolded the changes so you can see what I did and add more fields yourself later on). Let me know if this doesn't make sense.
SELECT
CT.EnterpriseName as EnterpriseName,
CTSG.DateTime as DateTime,
SUM(CTI.CallsOffered) as CallsOffered,
SUM(isnull(CTSG.AnswerWaitTime,0))/ sum(isnull(CTSG.CallsAnswered,0)) as ASA,
SUM(isnull(CTSG.AnswerWaitTime,0)) as AnswerWaitTime,
SUM(isnull(CTSG.CallsAnswered,0)) as CallsAnswered,
SUM(ISNULL(CTSG.CallsAnswered, 0)) as ACW,
SUM(ISNULL(CTSG.CallsAnswered, 0)) as ATT,
SUM(ISNULL(CTSG.CallsAnswered, 0)) as AHT,
SUM(ISNULL(CTSG.BusyOtherTime,0)) as BusyOtherTime,
SUM(ISNULL(CTSG.WorkReadyTime ,0)) as WorkReadyTime ,
SUM(ISNULL(CTSG.WorkNotReadyTime ,0)) as WorkNotReadyTime ,
SUM(ISNULL(CTSG.ReservedStateTime,0)) as ReservedStateTime,
--SUM(ISNULL(AI.AvailTime,0)) as AvailTime, --will add later
SUM(ISNULL(CTSG.TalkInTime,0) ) as TalkInTime,
SUM(ISNULL(CTSG.HoldTime,0)) as HoldTime
FROM
(SELECT
DateTime= Call_Type_SG_Interval.DateTime,
CallTypeID= CallTypeID,
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
CallsAnswered= sum(isnull( CallsAnswered,0)),
callsHandled= sum(isnull(CallsHandled,0)),
handleTime= sum(isnull(HandleTime,0)),
talkTime= sum(isnull(TalkTime,0)),
callsOfferedRouted=sum(isnull(CallsOfferedRouted,0)),
callsOfferedNotRouted=sum(isnull(CallsOfferedNotRouted,0)),
overflowOut=sum(isnull(OverflowOut,0)),
att= sum(isnull(TalkTime,0))/ sum(isnull(CallsHandled,0)),
aht= sum(isnull(HandleTime,0))/ sum(isnull(CallsHandled,0)),
asa= sum(isnull(AnswerWaitTime,0))/ sum(isnull(CallsAnswered,0))
WorkNotReadyTime = SUM(ISNULL(ASGI.WorkNotReadyTime,0)),
WorkReadyTime = SUM(ISNULL(ASGI.WorkReadyTime,0)),
TalkInTime = SUM(ISNULL(ASGI.TalkInTime,0)),
TalkOutTime= SUM(ISNULL(ASGI.TalkOutTime,0)),
AgentOutCalls= SUM(ISNULL(ASGI.AgentOutCalls,0)),
AvailTime= SUM(ISNULL(ASGI.AvailTime,0)),
HoldTime = SUM(ISNULL(ASGI.HoldTime,0)),
BusyOtherTime= SUM(ISNULL(ASGI.BusyOtherTime,0)),
ReservedStateTime = SUM(ISNULL(ASGI.ReservedStateTime,0))
FROM Call_Type_SG_Interval
LEFT OUTER JOIN (SELECT
SkillGroupSkillTargetID,
DateTime= DateTime,
WorkNotReadyTime = SUM(ISNULL(WorkNotReadyTime,0)),
WorkReadyTime = SUM(ISNULL(WorkReadyTime,0)),
TalkInTime = SUM(ISNULL(TalkInTime,0)),
TalkOutTime= SUM(ISNULL(TalkOutTime,0)),
AgentOutCalls= SUM(ISNULL(AgentOutCalls,0)),
AvailTime= SUM(ISNULL(AvailTime,0)),
HoldTime = SUM(ISNULL(HoldTime,0)),
BusyOtherTime= SUM(ISNULL(BusyOtherTime,0)),
ReservedStateTime = SUM(ISNULL(ReservedStateTime,0))
FROM Agent_Skill_Group_Interval
WHERE DateTime >= :startDate
and DateTime <= :endDate
GROUP BY SkillGroupSkillTargetID, DateTime
) ASGI
ON ASGI.SkillGroupSkillTargetID = Call_Type_SG_Interval.SkillGroupSkillTargetID AND ASGI.DateTime = Call_Type_SG_Interval.DateTime
WHERE CallTypeID IN (:CallTypeID)
and Call_Type_SG_Interval.DateTime >= :startDate
and Call_Type_SG_Interval.DateTime <= :endDate
GROUP BY Call_Type_SG_Interval.DateTime, CallTypeID
) CTSG
LEFT JOIN Call_Type CT (nolock)
ON CT.CallTypeID = CTSG.CallTypeID
LEFT OUTER JOIN (SELECT
DateTime= DateTime,
CallTypeID,
CallsOffered=SUM(ISNULL(CallsOffered,0)),
TalkTime = SUM(isnull(TalkTime,0)),
HoldTime = SUM(isnull(HoldTime,0))
FROM Call_Type_Interval
GROUP BY CallTypeID, DateTime
) CTI
ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime
WHERE CTSG.CallTypeID IN (:CallTypeID)
AND CTSG.DateTime >= :startDate
AND CTSG.DateTime <= :endDate
GROUP BY CTSG.CallTypeID, CT.EnterpriseName, CTSG.DateTime, CTSG.DateTime
ORDER BY CT.EnterpriseName, CTSG.DateTime