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?
Works like a charm - and it makes sense (I think, this is way over my head...)
However, I created another problem - I need to count in some Not Ready Reasoncodes from Agent_Event_Detail, and this messed upp CallsOffered again... Tried adding the OUTERJOIN both inside and outside the nested table with the same results.
Any idea?
I promise, this is the very last issue I need help with
THANKS a lot for your help, can´t tell you how much I appreciate it!
Here´s the code at the moment, see the new inner join in bold.
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,
SkillTargetID= ASGI.SkillTargetID,
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,
SkillTargetID = SkillTargetID,
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, SkillTargetID
) ASGI
ON ASGI.SkillGroupSkillTargetID = Call_Type_SG_Interval.SkillGroupSkillTargetID AND ASGI.DateTime = Call_Type_SG_Interval.DateTime
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(ISNULL(Duration,0))
FROM Agent_Event_Detail
WHERE DateTime >= :startDate
and DateTime <= :endDate
and ReasonCode IN ('71','1','101','120','43','57','85')
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=Call_Type_SG_Interval.DateTime AND AED.SkillTargetID=ASGI.SkillTargetID
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, ASGI.SkillTargetID
) 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,
Just an fyi I'm not going to have good access to my email for the weekend so I may be slow in responding to work this out. The first thing I notice is that you are joining AED on two different values for two different tables in the left outer join. I would just use ASGI for this, instead of joining to CTSG on DateTime. Apart from that it looks like this should work just fine. If changing that doesn't fix it then we can go from there.
So
) AED
ON AED.Interval=ASGI.DateTime AND AED.SkillTargetID=ASGI.SkillTargetID
Hey Richard,
No worries - I´ll be on holiday from today and two weeks forward.
Unfortunately it didnt fix the issue - the CallsOffered is really high.. hmm.
Thanks again!
Hey Robert,
Took a second look at this today and found your issue. What you didn't highlight was that you added SkillTargetID= ASGI.SkillTargetID to the CTSG Select statement and Group By.
(SELECT
DateTime= Call_Type_SG_Interval.DateTime,
SkillTargetID= ASGI.SkillTargetID,
CallTypeID= CallTypeID,
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
AND
GROUP BY Call_Type_SG_Interval.DateTime, CallTypeID, ASGI.SkillTargetID
This is what was causing your new issue (though to be safe I would still join AED and ASGI on DateTime instead of CTSG). Basically what adding that does is create a row with ALL of the CTSG data for each agent, and then in the big select statement (the one that calls all data you actually see) it sums those again. You don't want to add Group By's like that into a statement like this since otherwise you'll get those huge data distortions. Luckily, it's easy to fix. All you have to do is get rid of those two pieces and you'll be all set. You don't have to remove it from the WHERE clause, since calling something in the WHERE clause doesn't mean you need to Select it. Your CTSG table should look like this once you make the fix (or you can just copy and paste this).
(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,
SkillTargetID = SkillTargetID,
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, SkillTargetID
) ASGI
ON ASGI.SkillGroupSkillTargetID = Call_Type_SG_Interval.SkillGroupSkillTargetID AND ASGI.DateTime = Call_Type_SG_Interval.DateTime
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(ISNULL(Duration,0))
FROM Agent_Event_Detail
WHERE DateTime >= :startDate
and DateTime <= :endDate
and ReasonCode IN ('71','1','101','120','43','57','85')
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=ASGI.DateTime AND AED.SkillTargetID=ASGI.SkillTargetID
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
Richard,
Brilliant, thank you! This solved the issue with CallsOffered.
However, it seems to show the wrong figures for CallsAnswered - it shows really really high figures from this column.
EDIT: I got it working by using the CTI table instead of the Call_Type_SG_Interval
Thank you so much guys for your excellent help!
Cheers,
Robert
Hi again,
Never ending story, really...!
I noticed the loggedintime, ready time and so on, are really high because this report will run on every Team, so I had to add another parameter to choose team name. This didn´t make any difference so I also added a Skill Group parameter.
However, it still shows really high numbers and I have no clue why. Could it be something wrong in the query?
See the bold text below, this is what I´ve added.
SELECT
CT.EnterpriseName as EnterpriseName,
SUM(isnull(CTI.CallsOffered,0)) as CallsOffered,
SUM(isnull(CTI.AnswerWaitTime,0)) as ASA,
SUM(isnull(CTI.AnswerWaitTime,0)) as AnswerWaitTime,
SUM(isnull(CTI.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.CallsAnswered, 0)) as perACW,
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(CTSG.AvailTime,0)) as AvailTime,
SUM(ISNULL(CTSG.TalkInTime,0) ) as TalkInTime,
SUM(ISNULL(CTSG.HoldTime,0)) as HoldTime,
SUM(ISNULL(CTSG.Duration, 0)) as DurationNotReady,
SUM(ISNULL(CTSG.OutTime, 0)) as OutTime,
SUM(ISNULL(CTSG.AgentOutCalls, 0)) as AgentOutCalls,
SUM(ISNULL(CTSG.AgentCallsAnswered, 0)) as AgentCallsAnswered,
SUM(CTI.TotalCallsAband) as TotalCallsAband,
SUM(CTI.per_aban) as per_aban,
SUM(CTI.perAnswered) as perAnswered,
SUM(CTI.ServiceLevel) as ServiceLevel,
SUM(CTI.Ans10) as Ans10,
SUM(CTI.OverflowOut) as OverflowOut,
SUM(CTI.Other) as Other,
SUM(CTI.Ans20) as Ans20,
SUM(CTI.CallDelayAbandTime) as CallDelayAbandTime,
MAX(CTI.MaxCallWaitTime) as MaxCallWaitTime,
sum(isnull(CTI.IncompleteCalls,0)) as IncompleteCalls,
SUM(ISNULL(CTI.TotalCallsAband,0)) as avg_aban_delay,
SUM(CTI.ServiceLevelCalls) as ServiceLevelCalls,
SUM(CTI.ServiceLevelCallsOffered)-SUM(CTI.IncompleteCalls) as ServiceLevelCallsOffered,
SUM(ISNULL(CTI.CallsAnswered,0)) as perASR,
SUM(ISNULL(CTSG.LoggedOnTime,0)) as LoggedOnTime,
SUM(ISNULL(CTSG.LoggedOnTime, 0)) as PerReady,
SUM(ISNULL(CTSG.LoggedOnTime, 0)) as PerNotReady
FROM
(SELECT
DateTime= Call_Type_SG_Interval.DateTime,
CallTypeID= CallTypeID,
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)),
aht= sum(isnull(HandleTime,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)),
Duration=SUM(ISNULL(AED.Duration,0)),
OutTime=SUM(ISNULL(AED.Duration,0)),
AgentCallsAnswered = SUM(ISNULL(ASGI.AgentCallsAnswered,0)),
LoggedOnTime=SUM(ISNULL(ASGI.LoggedOnTime,0))
FROM Call_Type_SG_Interval
LEFT OUTER JOIN (SELECT
SkillGroupSkillTargetID,
SkillTargetID = SkillTargetID,
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)),
LoggedOnTime= SUM(ISNULL(LoggedOnTime,0)),
HoldTime = SUM(ISNULL(HoldTime,0)),
BusyOtherTime= SUM(ISNULL(BusyOtherTime,0)),
ReservedStateTime = SUM(ISNULL(ReservedStateTime,0)),
AgentCallsAnswered = SUM(ISNULL(CallsAnswered,0))
FROM Agent_Skill_Group_Interval
WHERE SkillTargetID IN (:AgentSkillTargetID)
and SkillGroupSkillTargetID IN (:SkillGroup)
and DateTime >= :startDate
and DateTime <= :endDate
GROUP BY SkillGroupSkillTargetID, DateTime, SkillTargetID
) ASGI
ON ASGI.SkillGroupSkillTargetID = Call_Type_SG_Interval.SkillGroupSkillTargetID AND ASGI.DateTime = Call_Type_SG_Interval.DateTime
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(ISNULL(Duration,0))
FROM Agent_Event_Detail
WHERE DateTime >= :startDate
and DateTime <= :endDate
and ReasonCode IN ('71','1','101','120','43','57','85')
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=ASGI.DateTime AND AED.SkillTargetID=ASGI.SkillTargetID
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(ISNULL(Duration,0))
FROM Agent_Event_Detail
WHERE DateTime >= :startDate
and DateTime <= :endDate
and ReasonCode IN ('03','103','122','45','59','73','87')
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
) OutTime
ON OutTime.Interval=ASGI.DateTime AND OutTime.SkillTargetID=ASGI.SkillTargetID
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(ISNULL(LoggedOnTime,0)),
NotReadyTime=SUM(ISNULL(NotReadyTime,0)),
AvailTime=SUM(ISNULL(AvailTime,0))
FROM Agent_Interval
WHERE 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=ASGI.DateTime AND AI.SkillTargetID=ASGI.SkillTargetID
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,
TalkTime = SUM(isnull(TalkTime,0)),
HoldTime = SUM(isnull(HoldTime,0)),
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
CallsOffered=sum(isnull(CallsOffered , 0)),
CallsAnswered=sum(isnull(CallsAnswered , 0)),
OverflowOut=SUM(isnull(OverflowOut,0)),
asa= sum(isnull(AnswerWaitTime,0)),
TotalCallsAband = sum(isnull(TotalCallsAband, 0)),
per_aban = sum(isnull(TotalCallsAband, 0)),
perAnswered = sum(isnull(CallsAnswered , 0))+SUM(isnull(OverflowOut,0)),
ServiceLevel = sum(isnull(ServiceLevel,0)),
Ans10 = sum(isnull(AnsInterval1,0)+isnull(AnsInterval2,0)),
Ans20 = sum(isnull(AnsInterval1,0)+isnull(AnsInterval2,0)+isnull(AnsInterval3,0)+isnull(AnsInterval4,0)),
MaxCallWaitTime=sum(MaxCallWaitTime),
IncompleteCalls=sum(IncompleteCalls),
ServiceLevelCalls=sum(ServiceLevelCalls),
ServiceLevelCallsOffered=sum(isnull(ServiceLevelCallsOffered,0)),
CallDelayAbandTime=sum(CallDelayAbandTime),
Other = (sum(isnull(OverflowOut,0))),
ReturnBusy = sum(isnull(ReturnBusy,0)),
ReturnRing = sum(isnull(ReturnRing,0)),
ICRDefaultRouted = sum(isnull(ICRDefaultRouted,0)),
NetworkDefaultRouted = sum(isnull(NetworkDefaultRouted,0)),
CallsRONA = sum(isnull(CallsRONA,0)),
ReturnRelease = sum(isnull(ReturnRelease,0)),
CallsRoutedNonAgent = sum(isnull(CallsRoutedNonAgent,0)),
ShortCalls = sum(isnull(ShortCalls,0)),
AgentErrorCount = sum(isnull(AgentErrorCount,0)),
ErrorCount = sum(isnull(ErrorCount,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
ORDER BY CT.EnterpriseName
Hi Robert,
You're using ASGI to calculate logged on time instead of AI. ASGI has a logged on time for each skill group an agent is involved in so you'll have excessively high values for each agent. That said, with a Call Type report you are still likely to see very high logged on times, ready times, etc, as you are calculating this value across all agents involved in the call type. What exactly do you want to use these values for?
Richard
Hey!
Thanks, I changed it to AI but it still seem to be almost double login times than when running another report.
We have a (quite odd) set up where I need to add in some Reason Codes into Ready time, AHT, and so on. And what I want is one complete report which shows every metric we need to see on a daily basis.
So, even if I added parameters for both SkillGroupSkillTargetID and SkillTargetID it shows too high figures - am I doing something wrong?
SELECT
CT.EnterpriseName as EnterpriseName,
SUM(isnull(CTI.CallsOffered,0)) as CallsOffered,
SUM(isnull(CTI.AnswerWaitTime,0)) as ASA,
SUM(isnull(CTI.AnswerWaitTime,0)) as AnswerWaitTime,
SUM(isnull(CTI.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.CallsAnswered, 0)) as perACW,
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(CTSG.AvailTime,0)) as AvailTime,
SUM(ISNULL(CTSG.TalkInTime,0) ) as TalkInTime,
SUM(ISNULL(CTSG.HoldTime,0)) as HoldTime,
SUM(ISNULL(CTSG.Duration, 0)) as DurationNotReady,
SUM(ISNULL(CTSG.OutTime, 0)) as OutTime,
SUM(ISNULL(CTSG.AgentOutCalls, 0)) as AgentOutCalls,
SUM(ISNULL(CTSG.AgentCallsAnswered, 0)) as AgentCallsAnswered,
SUM(CTI.TotalCallsAband) as TotalCallsAband,
SUM(CTI.per_aban) as per_aban,
SUM(CTI.perAnswered) as perAnswered,
SUM(CTI.ServiceLevel) as ServiceLevel,
SUM(CTI.Ans10) as Ans10,
SUM(CTI.OverflowOut) as OverflowOut,
SUM(CTI.Other) as Other,
SUM(CTI.Ans20) as Ans20,
SUM(CTI.CallDelayAbandTime) as CallDelayAbandTime,
MAX(CTI.MaxCallWaitTime) as MaxCallWaitTime,
sum(isnull(CTI.IncompleteCalls,0)) as IncompleteCalls,
SUM(ISNULL(CTI.TotalCallsAband,0)) as avg_aban_delay,
SUM(CTI.ServiceLevelCalls) as ServiceLevelCalls,
SUM(CTI.ServiceLevelCallsOffered)-SUM(CTI.IncompleteCalls) as ServiceLevelCallsOffered,
SUM(ISNULL(CTI.CallsAnswered,0)) as perASR,
SUM(ISNULL(CTSG.LoggedOnTime,0))-SUM(ISNULL(CTSG.LunchTime,0)) as LoggedOnTime,
SUM(ISNULL(CTSG.LoggedOnTime, 0)) as PerReady,
SUM(ISNULL(CTSG.LoggedOnTime, 0)) as PerNotReady
FROM
(SELECT
DateTime= Call_Type_SG_Interval.DateTime,
CallTypeID= CallTypeID,
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)),
aht= sum(isnull(HandleTime,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)),
Duration=SUM(ISNULL(AED.Duration,0)),
OutTime=SUM(ISNULL(AED.Duration,0)),
AgentCallsAnswered = SUM(ISNULL(ASGI.AgentCallsAnswered,0)),
LoggedOnTime=SUM(ISNULL(AI.LoggedOnTime,0)),
LunchTime=SUM(ISNULL(LunchTime.Duration,0))
FROM Call_Type_SG_Interval
LEFT OUTER JOIN (SELECT
SkillGroupSkillTargetID,
SkillTargetID = SkillTargetID,
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)),
LoggedOnTime= SUM(ISNULL(LoggedOnTime,0)),
HoldTime = SUM(ISNULL(HoldTime,0)),
BusyOtherTime= SUM(ISNULL(BusyOtherTime,0)),
ReservedStateTime = SUM(ISNULL(ReservedStateTime,0)),
AgentCallsAnswered = SUM(ISNULL(CallsAnswered,0))
FROM Agent_Skill_Group_Interval
WHERE SkillTargetID IN (:AgentSkillTargetID)
and SkillGroupSkillTargetID IN (:SkillGroup)
and DateTime >= :startDate
and DateTime <= :endDate
GROUP BY SkillGroupSkillTargetID, DateTime, SkillTargetID
) ASGI
ON ASGI.SkillGroupSkillTargetID = Call_Type_SG_Interval.SkillGroupSkillTargetID AND ASGI.DateTime = Call_Type_SG_Interval.DateTime
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(ISNULL(Duration,0))
FROM Agent_Event_Detail
WHERE DateTime >= :startDate
and SkillTargetID IN (:AgentSkillTargetID)
and DateTime <= :endDate
and ReasonCode IN ('71','1','101','120','43','57','85')
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=ASGI.DateTime AND AED.SkillTargetID=ASGI.SkillTargetID
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(ISNULL(Duration,0))
FROM Agent_Event_Detail
WHERE DateTime >= :startDate
and SkillTargetID IN (:AgentSkillTargetID)
and DateTime <= :endDate
and ReasonCode IN ('03','103','122','45','59','73','87')
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
) OutTime
ON OutTime.Interval=ASGI.DateTime AND OutTime.SkillTargetID=ASGI.SkillTargetID
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(ISNULL(Duration,0))
FROM Agent_Event_Detail
WHERE DateTime >= :startDate
and SkillTargetID IN (:AgentSkillTargetID)
and DateTime <= :endDate
and ReasonCode IN ('07','107','126','49','63','77','91')
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
) LunchTime
ON LunchTime.Interval=ASGI.DateTime AND LunchTime.SkillTargetID=ASGI.SkillTargetID
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(ISNULL(LoggedOnTime,0)),
NotReadyTime=SUM(ISNULL(NotReadyTime,0)),
AvailTime=SUM(ISNULL(AvailTime,0))
FROM Agent_Interval
WHERE DateTime >= :startDate
and SkillTargetID IN (:AgentSkillTargetID)
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=ASGI.DateTime AND AI.SkillTargetID=ASGI.SkillTargetID
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,
TalkTime = SUM(isnull(TalkTime,0)),
HoldTime = SUM(isnull(HoldTime,0)),
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
CallsOffered=sum(isnull(CallsOffered , 0)),
CallsAnswered=sum(isnull(CallsAnswered , 0)),
OverflowOut=SUM(isnull(OverflowOut,0)),
asa= sum(isnull(AnswerWaitTime,0)),
TotalCallsAband = sum(isnull(TotalCallsAband, 0)),
per_aban = sum(isnull(TotalCallsAband, 0)),
perAnswered = sum(isnull(CallsAnswered , 0))+SUM(isnull(OverflowOut,0)),
ServiceLevel = sum(isnull(ServiceLevel,0)),
Ans10 = sum(isnull(AnsInterval1,0)+isnull(AnsInterval2,0)),
Ans20 = sum(isnull(AnsInterval1,0)+isnull(AnsInterval2,0)+isnull(AnsInterval3,0)+isnull(AnsInterval4,0)),
MaxCallWaitTime=sum(MaxCallWaitTime),
IncompleteCalls=sum(IncompleteCalls),
ServiceLevelCalls=sum(ServiceLevelCalls),
ServiceLevelCallsOffered=sum(isnull(ServiceLevelCallsOffered,0)),
CallDelayAbandTime=sum(CallDelayAbandTime),
Other = (sum(isnull(OverflowOut,0))),
ReturnBusy = sum(isnull(ReturnBusy,0)),
ReturnRing = sum(isnull(ReturnRing,0)),
ICRDefaultRouted = sum(isnull(ICRDefaultRouted,0)),
NetworkDefaultRouted = sum(isnull(NetworkDefaultRouted,0)),
CallsRONA = sum(isnull(CallsRONA,0)),
ReturnRelease = sum(isnull(ReturnRelease,0)),
CallsRoutedNonAgent = sum(isnull(CallsRoutedNonAgent,0)),
ShortCalls = sum(isnull(ShortCalls,0)),
AgentErrorCount = sum(isnull(AgentErrorCount,0)),
ErrorCount = sum(isnull(ErrorCount,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
ORDER BY CT.EnterpriseName
Hey Robert,
I figured out your problem. Because you are joining AI, ASGI, and CTSG in the same query where you have yet to start using group bys, you are still joining AI's log in time to every skill group, meaning you're going to have high values. The problem is that because CTSG is primarily based on skill group and that's how you are joining to ASGI, there is no easy way to get around this and get an accurate result. This is why it's generally undesirable to join Agent and Call Type information because they don't play well together.
The issue you're running up against is that when you try and combine log in time with skill groups, you have to combine with each skill group. One way around this is to actually divide the login time by the number of skill groups involved. However, if an agent doesn't spend their whole login time in all skill groups this won't give you an accurate number. Another possibility is summing the max times for each agent per skill group, but still you're going to run up against this same issue because of how you're looking at this information. Let me take the weekend to think on this and get back to you. Whatever the solution is will require some fancy footwork.
Cheers,
Richard
Hey Robert,
So I've looked into this a bit more. Unfortunately, any sort of join with agent data is going to be next to impossible to get right. An agent can be involved with a number of different call types throughout the day, and even during an interval, so getting the log in time for agents per call type is difficult if not impossible. However, there's another way around this which might work for your purposes.
While you can't get a log in/not ready/ready value per call type, you can get the overall value for the date time interval. So if you're grouping by call type in the view, you can get an accurate number and summary per call type of the amount of time agents are overall logged in for. Every instance of your Call Type for a single interval will have the same login time as all the other Call Types for that interval, but each interval should accurately reflect your times. The way you do this is you join Agent Interval and the other necessary tables at the same time as you join CTI to CTSG. So your query would look like the below. Hope this works for your purposes.
SELECT
CT.EnterpriseName as EnterpriseName,
SUM(isnull(CTI.CallsOffered,0)) as CallsOffered,
SUM(isnull(CTI.AnswerWaitTime,0)) as ASA,
SUM(isnull(CTI.AnswerWaitTime,0)) as AnswerWaitTime,
SUM(isnull(CTI.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.CallsAnswered, 0)) as perACW,
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(CTSG.AvailTime,0)) as AvailTime,
SUM(ISNULL(CTSG.TalkInTime,0) ) as TalkInTime,
SUM(ISNULL(CTSG.HoldTime,0)) as HoldTime,
SUM(ISNULL(AI.Duration, 0)) as DurationNotReady,
SUM(ISNULL(AI.OutTime, 0)) as OutTime,
SUM(ISNULL(CTSG.AgentOutCalls, 0)) as AgentOutCalls,
SUM(ISNULL(CTSG.AgentCallsAnswered, 0)) as AgentCallsAnswered,
SUM(CTI.TotalCallsAband) as TotalCallsAband,
SUM(CTI.per_aban) as per_aban,
SUM(CTI.perAnswered) as perAnswered,
SUM(CTI.ServiceLevel) as ServiceLevel,
SUM(CTI.Ans10) as Ans10,
SUM(CTI.OverflowOut) as OverflowOut,
SUM(CTI.Other) as Other,
SUM(CTI.Ans20) as Ans20,
SUM(CTI.CallDelayAbandTime) as CallDelayAbandTime,
MAX(CTI.MaxCallWaitTime) as MaxCallWaitTime,
sum(isnull(CTI.IncompleteCalls,0)) as IncompleteCalls,
SUM(ISNULL(CTI.TotalCallsAband,0)) as avg_aban_delay,
SUM(CTI.ServiceLevelCalls) as ServiceLevelCalls,
SUM(CTI.ServiceLevelCallsOffered)-SUM(CTI.IncompleteCalls) as ServiceLevelCallsOffered,
SUM(ISNULL(CTI.CallsAnswered,0)) as perASR,
SUM(ISNULL(AI.LoggedOnTime,0))-SUM(ISNULL(AI.LunchTime,0)) as LoggedOnTime,
SUM(ISNULL(AI.LoggedOnTime, 0)) as PerReady,
SUM(ISNULL(AI.LoggedOnTime, 0)) as PerNotReady
FROM
(SELECT
DateTime= Call_Type_SG_Interval.DateTime,
CallTypeID= CallTypeID,
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)),
aht= sum(isnull(HandleTime,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)),
AgentCallsAnswered = SUM(ISNULL(ASGI.AgentCallsAnswered,0))
FROM Call_Type_SG_Interval
LEFT OUTER JOIN (SELECT
SkillGroupSkillTargetID,
SkillTargetID = SkillTargetID,
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)),
LoggedOnTime= SUM(ISNULL(LoggedOnTime,0)),
HoldTime = SUM(ISNULL(HoldTime,0)),
BusyOtherTime= SUM(ISNULL(BusyOtherTime,0)),
ReservedStateTime = SUM(ISNULL(ReservedStateTime,0)),
AgentCallsAnswered = SUM(ISNULL(CallsAnswered,0))
FROM Agent_Skill_Group_Interval
WHERE SkillTargetID IN (:AgentSkillTargetID)
and SkillGroupSkillTargetID IN (:SkillGroup)
and DateTime >= :startDate
and DateTime <= :endDate
GROUP BY SkillGroupSkillTargetID, DateTime, SkillTargetID
) 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,
TalkTime = SUM(isnull(TalkTime,0)),
HoldTime = SUM(isnull(HoldTime,0)),
AnswerWaitTime=sum(isnull(AnswerWaitTime,0)),
CallsOffered=sum(isnull(CallsOffered , 0)),
CallsAnswered=sum(isnull(CallsAnswered , 0)),
OverflowOut=SUM(isnull(OverflowOut,0)),
asa= sum(isnull(AnswerWaitTime,0)),
TotalCallsAband = sum(isnull(TotalCallsAband, 0)),
per_aban = sum(isnull(TotalCallsAband, 0)),
perAnswered = sum(isnull(CallsAnswered , 0))+SUM(isnull(OverflowOut,0)),
ServiceLevel = sum(isnull(ServiceLevel,0)),
Ans10 = sum(isnull(AnsInterval1,0)+isnull(AnsInterval2,0)),
Ans20 = sum(isnull(AnsInterval1,0)+isnull(AnsInterval2,0)+isnull(AnsInterval3,0)+isnull(AnsInterval4,0)),
MaxCallWaitTime=sum(MaxCallWaitTime),
IncompleteCalls=sum(IncompleteCalls),
ServiceLevelCalls=sum(ServiceLevelCalls),
ServiceLevelCallsOffered=sum(isnull(ServiceLevelCallsOffered,0)),
CallDelayAbandTime=sum(CallDelayAbandTime),
Other = (sum(isnull(OverflowOut,0))),
ReturnBusy = sum(isnull(ReturnBusy,0)),
ReturnRing = sum(isnull(ReturnRing,0)),
ICRDefaultRouted = sum(isnull(ICRDefaultRouted,0)),
NetworkDefaultRouted = sum(isnull(NetworkDefaultRouted,0)),
CallsRONA = sum(isnull(CallsRONA,0)),
ReturnRelease = sum(isnull(ReturnRelease,0)),
CallsRoutedNonAgent = sum(isnull(CallsRoutedNonAgent,0)),
ShortCalls = sum(isnull(ShortCalls,0)),
AgentErrorCount = sum(isnull(AgentErrorCount,0)),
ErrorCount = sum(isnull(ErrorCount,0))
FROM Call_Type_Interval
GROUP BY CallTypeID, DateTime
) CTI
ON CTI.CallTypeID = CTSG.CallTypeID AND CTI.DateTime = CTSG.DateTime
LEFT OUTER JOIN (SELECT AI.Interval,
Duration=SUM(ISNULL(AED.Duration,0)),
OutTime=SUM(ISNULL(AED.Duration,0)),
LoggedOnTime=SUM(ISNULL(AI.LoggedOnTime,0)),
NotReadyTime=SUM(ISNULL(AI.NotReadyTime,0)),
AvailTime=SUM(ISNULL(AI.AvailTime,0)),
LunchTime=SUM(ISNULL(LunchTime.Duration,0))
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,
LoggedOnTime=SUM(ISNULL(LoggedOnTime,0)),
NotReadyTime=SUM(ISNULL(NotReadyTime,0)),
AvailTime=SUM(ISNULL(AvailTime,0))
FROM Agent_Interval
WHERE DateTime >= :startDate
and SkillTargetID IN (:AgentSkillTargetID)
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
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(ISNULL(Duration,0))
FROM Agent_Event_Detail
WHERE DateTime >= :startDate
and SkillTargetID IN (:AgentSkillTargetID)
and DateTime <= :endDate
and ReasonCode IN ('71','1','101','120','43','57','85')
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=AI.Interval AND AED.SkillTargetID=AI.SkillTargetID
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(ISNULL(Duration,0))
FROM Agent_Event_Detail
WHERE DateTime >= :startDate
and SkillTargetID IN (:AgentSkillTargetID)
and DateTime <= :endDate
and ReasonCode IN ('03','103','122','45','59','73','87')
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
) OutTime
ON OutTime.Interval=AI.Interval AND OutTime.SkillTargetID=AI.SkillTargetID
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(ISNULL(Duration,0))
FROM Agent_Event_Detail
WHERE DateTime >= :startDate
and SkillTargetID IN (:AgentSkillTargetID)
and DateTime <= :endDate
and ReasonCode IN ('07','107','126','49','63','77','91')
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
) LunchTime
ON LunchTime.Interval=AI.Interval AND LunchTime.SkillTargetID=AI.SkillTargetID
GROUP BY AI.Interval
)
AI ON AI.Interval = CTSG.DateTime
WHERE CTSG.CallTypeID IN (:CallTypeID)
AND CTSG.DateTime >= :startDate
AND CTSG.DateTime <= :endDate
GROUP BY CTSG.CallTypeID, CT.EnterpriseName
ORDER BY CT.EnterpriseName