Combining CT and SG data

[roberteliasson]

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?

[roberteliasson]

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

[richapar]

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

[roberteliasson]

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!

[richapar]

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

[roberteliasson]

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

[roberteliasson]

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

[richapar]

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

[roberteliasson]

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

[richapar]

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

[richapar]

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