I am using this Query. it is default cisco template.
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
SELECT Media = Media_Routing_Domain.EnterpriseName,
MRDomainID = Media_Routing_Domain.MRDomainID,
Attribute1=A1.EnterpriseName,
Attribute2=A2.EnterpriseName,
Attribute3=A3.EnterpriseName,
Attribute4=A4.EnterpriseName,
Attribute5=A5.EnterpriseName,
Attribute6=A6.EnterpriseName,
Attribute7=A7.EnterpriseName,
Attribute8=A8.EnterpriseName,
Attribute9=A9.EnterpriseName,
Attribute10=A10.EnterpriseName,
Interval=S.DateTime,
DATE=CONVERT(char(10),S.DateTime,101),
FullName=PQ.EnterpriseName,
PrecisionQueueID=PQ.PrecisionQueueID,
CallbackMessages=SUM(S.CbM),
CallbackMessagesTime=SUM(S.CbMT),
AvgHandledCallsTalkTime=AVG(S.AHCTT),
HoldTime=SUM(S.HT),
HandledCallsTalkTime=SUM(S.HCTT),
InternalCalls=SUM(S.IntC),
InternalCallsTime=SUM(S.IntCT),
CallsHandled=SUM(S.CH),
SupervAssistCalls=SUM(S.SAC),
AvgHandledCallsTime=AVG(S.AHCT),
SupervAssistCallsTime=SUM(S.SACT),
HandledCallsTime=SUM(S.HCT),
AgentOutCallsTime=SUM(S.AOCT),
TalkInTime=SUM(S.**bleep**),
LoggedOnTime=SUM(S.LOT),
ExternalOut=SUM(S.EO),
TalkOutTime=SUM(S.TOutT),
TalkOtherTime=SUM(S.TOT),
AvailTime=SUM(S.AT),
NotReadyTime=SUM(S.NRT),
TransferInCalls=SUM(S.TIC),
TalkTime=SUM(S.TT),
TransferInCallsTime=SUM(S.TICT),
WorkReadyTime=SUM(S.WRT),
TransferOutCalls=SUM(S.TOC),
WorkNotReadyTime=SUM(S.WNRT),
BusyOtherTime=SUM(S.BOT),
CallsAnswered=SUM(S.CA),
ReservedStateTime=SUM(S.RST),
AnswerWaitTime=SUM(S.AWT),
AbandonRingCalls=SUM(S.ARC),
AbandonRingTime=SUM(S.ART),
AbandonHoldCalls=SUM(S.AHC),
AgentOutCallsTalkTime=SUM(S.AOCTT),
AgentOutCallsOnHold=SUM(S.AOCOH),
AgentOutCallsOnHoldTime=SUM(S.AOCOHT),
AgentTerminatedCalls=SUM(S.ATC),
ConsultativeCalls=SUM(S.CC),
ConsultativeCallsTime=SUM(S.CCT),
ConferencedInCalls=SUM(S.CIC),
ConferencedInCallsTime=SUM(S.CICT),
ConferencedOutCalls=SUM(S.COC),
ConferencedOutCallsTime=SUM(S.COCT),
IncomingCallsOnHoldTime=SUM(S.ICOHT),
IncomingCallsOnHold=SUM(S.ICOH),
InternalCallsOnHoldTime=SUM(S.IntCOHT),
InternalCallsOnHold=SUM(S.IntCOH),
InternalCallsRcvdTime=SUM(S.IntCRT),
InternalCallsRcvd=SUM(S.IntCR),
RedirectNoAnsCalls=SUM(S.RNAC),
RedirectNoAnsCallsTime=SUM(S.RNACT),
ShortCalls=SUM(S.SC),
CallsAbandQ=SUM(ISNULL(R.CallsAbandQ,0)),
QueueCalls=SUM(ISNULL(R.QueueCalls,0)),
AutoOutCalls=SUM(S.AutoOC),
AutoOutCallsTime=SUM(S.AutoOCT),
AutoOutCallsTalkTime=SUM(S.AutoOCTT),
AutoOutCallsOnHold=SUM(S.AutoOCOH),
AutoOutCallsOnHoldTime=SUM(S.AutoOCOHT),
PreviewCalls=SUM(S.PC),
PreviewCallsTime=SUM(S.PCT),
PreviewCallsTalkTime=SUM(S.PCTT),
PreviewCallsOnHold=SUM(S.PCOH),
PreviewCallsOnHoldTime=SUM(S.PCOHT),
ReserveCalls=SUM(S.RC),
ReserveCallsTime=SUM(S.RCT),
ReserveCallsTalkTime=SUM(S.RCTT),
ReserveCallsOnHold=SUM(S.RCOH),
ReserveCallsOnHoldTime=SUM(S.RCOHT),
TalkAutoOutTime=SUM(S.TAOT),
TalkPreviewTime=SUM(S.TPT),
TalkReserveTime=SUM(S.TRT),
BargeInCalls=SUM(S.BIC),
InterceptCalls=SUM(S.IC),
MonitorCalls=SUM(S.MC),
WhisperCalls=SUM(S.WC),
EmergencyAssists=SUM(S.EA),
CallsOffered=SUM(ISNULL(R.CallsOffered,0)),
CallsQueued=SUM(ISNULL(R.QueueCalls,0)),
InterruptedTime=SUM(S.IT),
TimeZone=R.TimeZone,
CallsAgentAbandons=SUM(ISNULL(R.CallsAbandToAgent,0)),
CallsDequeued=SUM(ISNULL(R.CallsDequeued,0)),
RouterError=SUM(S.RE),
DoNotUseSLTop=CASE min(isnull(PQ.ServiceLevelType,0))
WHEN 1 THEN sum(isnull(R.ServiceLevelCalls,0)) * 1.0
WHEN 2 THEN sum(isnull(R.ServiceLevelCalls,0)) * 1.0
WHEN 3 THEN (sum(isnull(R.ServiceLevelCalls,0)) + sum(isnull(R.ServiceLevelCallsAband,0))) * 1.0
ELSE 0 END,
DoNotUseSLBottom=CASE min(isnull(PQ.ServiceLevelType,0))
WHEN 1 THEN CASE WHEN (sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0)) - sum(isnull(R.ServiceLevelCallsAband,0))) <= 0 THEN 0 ELSE (sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.ServiceLevelCallsAband,0))) END
WHEN 2 THEN CASE WHEN (sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0))) <= 0 THEN 0 ELSE (sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0))) END
WHEN 3 THEN CASE WHEN (sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0))) <= 0 THEN 0 ELSE (sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0))) END
ELSE 0 END,
ServicelLevel=CASE min(isnull(PQ.ServiceLevelType,0))
WHEN 1 THEN CASE WHEN (sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0)) - sum(isnull(R.ServiceLevelCallsAband,0))) <= 0 THEN 0 ELSE
sum(isnull(R.ServiceLevelCalls,0)) * 1.0 /
(sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0)) - sum(isnull(R.ServiceLevelCallsAband,0))) END
WHEN 2 THEN CASE WHEN (sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0))) <= 0 THEN 0 ELSE
sum(isnull(R.ServiceLevelCalls,0)) * 1.0 /
(sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0))) END
WHEN 3 THEN CASE WHEN (sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0))) <= 0 THEN 0 ELSE
(sum(isnull(R.ServiceLevelCalls,0)) + sum(isnull(R.ServiceLevelCallsAband,0))) * 1.0 /
(sum(isnull(R.ServiceLevelCallsOffered,0)) - sum(isnull(R.CallsDequeued,0)) - sum(isnull(R.CallsAbandDequeued,0))) END
ELSE 0 END,
ServiceLevelCalls=SUM(ISNULL(R.ServiceLevelCalls,0)),
ServiceLevelCallsAband=SUM(ISNULL(R.ServiceLevelCallsAband,0)),
ServiceLevelCallsDequeue=SUM(ISNULL(R.ServiceLevelCallsDequeue,0)),
ServiceLevelError= SUM(ISNULL(R.ServiceLevelError,0)),
ServiceLevelRONA=SUM(ISNULL(R.ServiceLevelRONA,0)),
ServiceLevelCallsOffered=SUM(ISNULL(R.ServiceLevelCallsOffered,0)),
NetConsultativeCalls=sum(S.NCC),
NetConsultativeCallsTime=SUM(S.NCCT),
NetConferencedOutCalls=sum(S.NCOC),
NetConfOutCallsTime=SUM(S.NCOCT),
NetTransferredOutCalls=sum(S.NTOC),
TotalComplete=SUM(S.HalfComplete)+SUM(ISNULL(R.CallsAbandQ,0))+SUM(ISNULL(R.RouterError,0))+SUM(ISNULL(R.CallsAbandToAgent,0)),
MaxCallsQueued=max(isnull(R.MaxCallsQueued,0)),
MaxCallWaitTime=max(isnull(R.MaxCallWaitTime,0)),
ReportingInterval=SUM(S.RI)*60,
fte_AgentsLogonTotal=SUM(S.LOT) * 1.0 / (SUM(S.RI)*60),
fte_AgentsNotReady=SUM(S.NRT) * 1.0 / (SUM(S.RI)*60),
fte_AgentsNotActive=SUM(S.AT) * 1.0 / (SUM(S.RI)*60),
fte_AgentsActive=SUM(S.TT) * 1.0 / (SUM(S.RI)*60),
fte_AgentsWrapup=SUM(S.WRT+S.WNRT) * 1.0 / (SUM(S.RI)*60),
fte_AgentsOther=SUM(S.BOT) * 1.0 / (SUM(S.RI)*60),
fte_AgentsHold=SUM(S.HT) * 1.0 / (SUM(S.RI)*60),
fte_AgentsReserved=SUM(S.RST) * 1.0 / (SUM(S.RI)*60) ,
ast_PercentNotActiveTime=(CASE WHEN SUM(S.LOT)=0 THEN 0
ELSE SUM(S.AT) * 1.0 / SUM(S.LOT) END),
ast_PercentActiveTime=(CASE WHEN SUM(S.LOT)=0 THEN 0
ELSE SUM(S.TT) * 1.0 / SUM(S.LOT) END),
ast_PercentHoldTime=(CASE WHEN SUM(S.LOT)=0 THEN 0
ELSE SUM(S.HT) * 1.0 / SUM(S.LOT) END),
ast_PercentWrapTime=(CASE WHEN SUM(S.LOT)=0 THEN 0
ELSE (SUM(S.WNRT + S.WRT)) * 1.0 / SUM(S.LOT) END),
ast_PercentReservedTime=(CASE WHEN SUM(S.LOT)=0 THEN 0
ELSE SUM(S.RST) * 1.0 / SUM(S.LOT) END),
ast_PercentNotReadyTime=(CASE WHEN SUM(S.LOT)=0 THEN 0
ELSE SUM(S.NRT) * 1.0 / SUM(S.LOT) END),
ast_PercentUtilization=
CASE WHEN (SUM(S.LOT) - SUM(S.NRT))=0 THEN 0
ELSE (CASE WHEN SUM(S.TT)=0 THEN 0 ELSE (SUM(S.**bleep**) +
SUM(S.TOutT) +
SUM(S.TOT) +
SUM(S.WRT) +
SUM(S.WNRT)) * 1.0 /
(SUM(S.LOT) - SUM(S.NRT)) END) END,
asa= CASE WHEN SUM(S.CA)=0 THEN 0
ELSE SUM(S.AWT) * 1.0 / SUM(S.CA) END,
CompletedTasks_AHT= (CASE WHEN SUM(S.CH)=0 THEN 0
ELSE SUM(S.HCT) / SUM(S.CH) END),
CompletedTasks_AvgActiveTime=CASE WHEN SUM(S.CH)=0 THEN 0 ELSE (SUM(S.HCTT) / SUM(S.CH))END,
CompletedTasks_AvgWrapTime=CASE WHEN SUM(S.CH)=0 THEN 0 ELSE ((SUM(S.WRT) + SUM(S.WNRT)) / SUM(S.CH))END,
ast_ActiveTime=SUM(S.TT),
ast_PerBusyOtherTime=(CASE WHEN SUM(S.LOT)=0 THEN 0
ELSE SUM(S.BOT) * 1.0 / SUM(S.LOT) END),
AbandCalls=SUM(R.CallsAbandQ + R.CallsAbandToAgent)
FROM
(Select PrecisionQueueID, DateTime,
CbM=SUM(ISNULL(CallbackMessages,0)),
CbMT=SUM(ISNULL(CallbackMessagesTime,0)),
AHCTT=AVG(ISNULL(AvgHandledCallsTalkTime,0)),
HT=SUM(ISNULL(HoldTime,0)),
HCTT=SUM(ISNULL(HandledCallsTalkTime,0)),
IntC=SUM(ISNULL(InternalCalls,0)),
IntCT=SUM(ISNULL(InternalCallsTime,0)),
CH=SUM(ISNULL(CallsHandled,0)),
SAC=SUM(ISNULL(SupervAssistCalls,0)),
AHCT=AVG(ISNULL(AvgHandledCallsTime,0)),
SACT=SUM(ISNULL(SupervAssistCallsTime,0)),
HCT=SUM(ISNULL(HandledCallsTime,0)),
AOCT=SUM(ISNULL(AgentOutCallsTime,0)),
**bleep**=SUM(ISNULL(TalkInTime,0)),
LOT=SUM(ISNULL(LoggedOnTime,0)),
EO=SUM(ISNULL(AgentOutCalls,0)),
TOutT=SUM(ISNULL(TalkOutTime,0)),
TOT=SUM(ISNULL(TalkOtherTime,0)),
AT=SUM(ISNULL(AvailTime,0)),
NRT=SUM(ISNULL(NotReadyTime,0)),
TIC=SUM(ISNULL(TransferInCalls,0)),
TT=SUM(ISNULL(TalkTime,0)),
TICT=SUM(ISNULL(TransferInCallsTime,0)),
WRT=SUM(ISNULL(WorkReadyTime,0)),
TOC=SUM(ISNULL(TransferOutCalls,0)),
WNRT=SUM(ISNULL(WorkNotReadyTime,0)),
BOT=SUM(ISNULL(BusyOtherTime,0)),
CA=SUM(ISNULL(CallsAnswered,0)),
RST=SUM(ISNULL(ReservedStateTime,0)),
AWT=SUM(ISNULL(AnswerWaitTime,0)),
ARC=SUM(ISNULL(AbandonRingCalls,0)),
ART=SUM(ISNULL(AbandonRingTime,0)),
AHC=SUM(ISNULL(AbandonHoldCalls,0)),
AOCTT=SUM(ISNULL(AgentOutCallsTalkTime,0)),
AOCOH=SUM(ISNULL(AgentOutCallsOnHold,0)),
AOCOHT=SUM(ISNULL(AgentOutCallsOnHoldTime,0)),
ATC=SUM(ISNULL(AgentTerminatedCalls,0)),
CC=SUM(ISNULL(ConsultativeCalls,0)),
CCT=SUM(ISNULL(ConsultativeCallsTime,0)),
CIC=SUM(ISNULL(ConferencedInCalls,0)),
CICT=SUM(ISNULL(ConferencedInCallsTime,0)),
COC=SUM(ISNULL(ConferencedOutCalls,0)),
COCT=SUM(ISNULL(ConferencedOutCallsTime,0)),
ICOHT=SUM(ISNULL(IncomingCallsOnHoldTime,0)),
ICOH=SUM(ISNULL(IncomingCallsOnHold,0)),
IntCOHT=SUM(ISNULL(InternalCallsOnHoldTime,0)),
IntCOH=SUM(ISNULL(InternalCallsOnHold,0)),
IntCRT=SUM(ISNULL(InternalCallsRcvdTime,0)),
IntCR=SUM(ISNULL(InternalCallsRcvd,0)),
RNAC=SUM(ISNULL(RedirectNoAnsCalls,0)),
RNACT=SUM(ISNULL(RedirectNoAnsCallsTime,0)),
SC=SUM(ISNULL(ShortCalls,0)),
AutoOC=SUM(ISNULL(AutoOutCalls,0)),
AutoOCT=SUM(ISNULL(AutoOutCallsTime,0)),
AutoOCTT=SUM(ISNULL(AutoOutCallsTalkTime,0)),
AutoOCOH=SUM(ISNULL(AutoOutCallsOnHold,0)),
AutoOCOHT=SUM(ISNULL(AutoOutCallsOnHoldTime,0)),
PC=SUM(ISNULL(PreviewCalls,0)),
PCT=SUM(ISNULL(PreviewCallsTime,0)),
PCTT=SUM(ISNULL(PreviewCallsTalkTime,0)),
PCOH=SUM(ISNULL(PreviewCallsOnHold,0)),
PCOHT=SUM(ISNULL(PreviewCallsOnHoldTime,0)),
RC=SUM(ISNULL(ReserveCalls,0)),
RCT=SUM(ISNULL(ReserveCallsTime,0)),
RCTT=SUM(ISNULL(ReserveCallsTalkTime,0)),
RCOH=SUM(ISNULL(ReserveCallsOnHold,0)),
RCOHT=SUM(ISNULL(ReserveCallsOnHoldTime,0)),
TAOT=SUM(ISNULL(TalkAutoOutTime,0)),
TPT=SUM(ISNULL(TalkPreviewTime,0)),
TRT=SUM(ISNULL(TalkReserveTime,0)),
BIC=SUM(ISNULL(BargeInCalls,0)),
IC=SUM(ISNULL(InterceptCalls,0)),
MC=SUM(ISNULL(MonitorCalls,0)),
WC=SUM(ISNULL(WhisperCalls,0)),
EA=SUM(ISNULL(EmergencyAssists,0)),
IT=SUM(ISNULL(InterruptedTime,0)),
RE=SUM(ISNULL(RouterError,0)),
NCC=sum(isnull(NetConsultativeCalls,0)),
NCCT=SUM(ISNULL(NetConsultativeCallsTime,0)),
NCOC=sum(isnull(NetConferencedOutCalls,0)),
NCOCT=SUM(ISNULL(NetConfOutCallsTime,0)),
NTOC=sum(isnull(NetTransferOutCalls,0)),
HalfComplete=SUM(ISNULL(CallsHandled,0))+SUM(ISNULL(RedirectNoAnsCalls,0)),
RI=SUM(ISNULL(ReportingInterval,0))
from Skill_Group_Interval (nolock)
group by PrecisionQueueID, DateTime) S LEFT OUTER JOIN
Router_Queue_Interval R (nolock) on R.DateTime = S.DateTime and R.PrecisionQueueID=S.PrecisionQueueID
LEFT OUTER JOIN Attribute A1 (nolock) on R.AttributeID1=A1.AttributeID
LEFT OUTER JOIN Attribute A2 (nolock) on R.AttributeID2=A2.AttributeID
LEFT OUTER JOIN Attribute A3 (nolock) on R.AttributeID3=A3.AttributeID
LEFT OUTER JOIN Attribute A4 (nolock) on R.AttributeID4=A4.AttributeID
LEFT OUTER JOIN Attribute A5 (nolock) on R.AttributeID5=A5.AttributeID
LEFT OUTER JOIN Attribute A6 (nolock) on R.AttributeID6=A6.AttributeID
LEFT OUTER JOIN Attribute A7 (nolock) on R.AttributeID7=A7.AttributeID
LEFT OUTER JOIN Attribute A8 (nolock) on R.AttributeID8=A8.AttributeID
LEFT OUTER JOIN Attribute A9 (nolock) on R.AttributeID9=A9.AttributeID
LEFT OUTER JOIN Attribute A10 (nolock) on R.AttributeID10=A10.AttributeID,
Precision_Queue PQ (nolock),
Media_Routing_Domain (nolock)
WHERE PQ.PrecisionQueueID IN (5075) and (DATEPART(dw, S.DateTime) in(2,3,4,5,6,7,1) and S.DateTime between '2020-02-23 00:00:00' and '2020-02-29 23:59:59' and convert([char], S.DateTime, 108) between '00:00:00' and '23:59:59') and PQ.PrecisionQueueID=S.PrecisionQueueID
and PQ.MRDomainID = Media_Routing_Domain.MRDomainID
GROUP BY
Media_Routing_Domain.EnterpriseName,
Media_Routing_Domain.MRDomainID,
A1.EnterpriseName,
A2.EnterpriseName,
A3.EnterpriseName,
A4.EnterpriseName,
A5.EnterpriseName,
A6.EnterpriseName,
A7.EnterpriseName,
A8.EnterpriseName,
A9.EnterpriseName,
A10.EnterpriseName,
PQ.EnterpriseName,
PQ.PrecisionQueueID,
S.DateTime,
CONVERT(char(10),S.DateTime,101),
R.TimeZone
ORDER BY PQ.EnterpriseName,
S.DateTime
