cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
732
Views
0
Helpful
10
Replies

How to see see which PQ's get the least/most inbound calls.

SubnetZero1
Level 1
Level 1

i want to see which PQ's get the least/most inbound calls.

 

 

Is there anyway to determine how many inbound calls went through a PQ on any specific day?

1 Accepted Solution

Accepted Solutions

SubnetZero1
Level 1
Level 1

Thanks all for your responses.

 

I ended up using a query from CUIC and modified it. I hope this helps someone in the future with similar issue.

 

        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=S.DateTime,
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(R.CallsHandled),
--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(R.CallsAnswered),
--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(R.RedirectNoAnsCalls),
--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(ISNULL(R.RouterError,0)),  
/*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),
--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(ISNULL(R.CallsAbandQ,0) + ISNULL(R.CallsAbandToAgent,0)),
--pickRequests = sum(isnull(R.PickRequests,0)),
--pickErrors = sum(isnull(R.PickErrors,0)),
--pullRequests = sum(isnull(R.PullRequests,0)),
TotalComplete=SUM(ISNULL(R.CallsHandled,0))+SUM(ISNULL(R.RedirectNoAnsCalls,0))+SUM(ISNULL(R.CallsAbandQ,0))+SUM(ISNULL(R.RouterError,0))+SUM(ISNULL(R.CallsAbandToAgent,0))
--pullErrors = sum(isnull(R.PullErrors,0))
FROM
Router_Queue_Interval R (nolock)
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,
(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)),
RI=MAX(ISNULL(ReportingInterval,0))
from Skill_Group_Interval (nolock)
group by PrecisionQueueID, DateTime) S, 
Precision_Queue PQ (nolock),
Media_Routing_Domain  (nolock)  
WHERE PQ.PrecisionQueueID IN (5055, 5056, 5057, 5061, 5069, 5070, 5071, 5080, 5081, 5082, 5083, 5084, 5085, 5114, 5115, 5116, 5117, 5118, 5122, 5123, 5124, 5140, 5154) 
--and (((S.DateTime between '2022-05-31 22:00:00' and '2022-05-31 23:59:59' and (DATEPART(dw, S.DateTime) in(1,2,3,4,5,6,7))) or (S.DateTime between '2022-06-08 00:00:00' and '2022-06-08 21:59:59' and (DATEPART(dw, S.DateTime) in(2,3,4,5,6,7,1))) or (S.DateTime between '2022-06-01 00:00:00' and '2022-06-07 23:59:59' and ((convert([char], S.DateTime, 108) between '22:00:00' and '23:59:59' and (DATEPART(dw, S.DateTime) in(1,2,3,4,5,6,7))) or (convert([char], S.DateTime, 108) between '00:00:00' and '21:59:59' and (DATEPART(dw, S.DateTime) in(2,3,4,5,6,7,1))))))) 
and S.DateTime BETWEEN '06-28-2022 00:00:00' AND '06-29-2022 00:00:00'
and PQ.PrecisionQueueID=S.PrecisionQueueID 
and R.DateTime=S.DateTime 
and PQ.PrecisionQueueID=R.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

View solution in original post

10 Replies 10

Sure, you could run a report like the Precision Queue All Fields report to find out which PQs are being offered the most/least volume.

I would do this on the call type level, but you can do it on the PQ too, but yes the CUIC report that @bill.king1 mentioned will be the easiest to get this information. You could look at the query it runs and then run that manually via SQL Studio.

 

david

Hello,

 

Thanks for your response.

 

 

I did find the report/SQL Query.

It displays what i want but not for the specific date range...

 

 

The query runs but when i add a date range, the query breaks on ms sql studio.

 

 

This is what i'm using. 

WHERE r.DateTime BETWEEN '07-10-2022 00:00:00' AND '07-12-2022 00:00:00'

AND Precision_Queue.PrecisionQueueID IN (5055, 5056, 5057, 5061, 5069, 5070, 5071, 5080, 5081, 5082, 5083, 5084, 5085, 5114, 5115, 5116, 5117, 5118, 5122, 5123, 5124, 5140, 5154) and  (PQRT.PrecisionQueueID = Precision_Queue.PrecisionQueueID)
AND  ( Media_Routing_Domain.MRDomainID = Precision_Queue.MRDomainID) 

 

 

 

 

 

That query is not complete, can you post the complete SQL query?

 

david

SET ANSI_WARNINGS OFF SET ARITHABORT OFF SET ARITHIGNORE ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF 
SELECT
DateTime,
PrecisionQueue = Precision_Queue.EnterpriseName,
PrecisionQueueID = Precision_Queue.PrecisionQueueID,
CallsAnsweredTo5 = SUM(ISNULL(PQRT.CallsAnsweredTo5, 0)),
CallsAband = SUM(ISNULL(PQRT.CallsAbandQTo5, 0)) + SUM(ISNULL(PQRT.CallsAbandToAgentTo5, 0)), 
Total_Volume  = SUM(ISNULL(PQRT.CallsOfferedTo5, 0)),
PercentAbandon =  case when SUM(ISNULL(PQRT.CallsOfferedTo5, 0)) = 0 then 0 
else (SUM(ISNULL(PQRT.CallsAbandQTo5, 0)) + SUM(ISNULL(PQRT.CallsAbandToAgentTo5, 0))) * 100.0/ SUM(ISNULL(PQRT.CallsOfferedTo5, 0)) end,
CallsQNow = SUM(ISNULL(PQRT.CallsQNow, 0))
FROM Precision_Queue (nolock),
         Media_Routing_Domain (nolock), 
	 Precision_Q_Real_Time PQRT (nolock)
  LEFT OUTER JOIN Attribute A1 (nolock) on PQRT.AttributeID1 = A1.AttributeID
  LEFT OUTER JOIN Attribute A2 (nolock) on PQRT.AttributeID2 = A2.AttributeID
  LEFT OUTER JOIN Attribute A3 (nolock) on PQRT.AttributeID3 = A3.AttributeID
  LEFT OUTER JOIN Attribute A4 (nolock) on PQRT.AttributeID4 = A4.AttributeID
  LEFT OUTER JOIN Attribute A5 (nolock) on PQRT.AttributeID5 = A5.AttributeID
  LEFT OUTER JOIN Attribute A6 (nolock) on PQRT.AttributeID6 = A6.AttributeID
  LEFT OUTER JOIN Attribute A7 (nolock) on PQRT.AttributeID7 = A7.AttributeID
  LEFT OUTER JOIN Attribute A8 (nolock) on PQRT.AttributeID8 = A8.AttributeID
  LEFT OUTER JOIN Attribute A9 (nolock) on PQRT.AttributeID9 = A9.AttributeID
  LEFT OUTER JOIN Attribute A10 (nolock) on PQRT.AttributeID10 = A10.AttributeID

   --WHERE DateTime BETWEEN '06-01-2022 00:00:00' AND '06-08-2022 00:00:00'
WHERE Precision_Queue.PrecisionQueueID IN (5055, 5056, 5057, 5061, 5069, 5070, 5071, 5080, 5081, 5082, 5083, 5084, 5085, 5114, 5115, 5116, 5117, 5118, 5122, 5123, 5124, 5140, 5154)
AND  (PQRT.PrecisionQueueID = Precision_Queue.PrecisionQueueID)
AND  ( Media_Routing_Domain.MRDomainID = Precision_Queue.MRDomainID) 

GROUP BY Media_Routing_Domain.EnterpriseName,
		Media_Routing_Domain.MRDomainID,
        Precision_Queue.EnterpriseName,   
		 Precision_Queue.PrecisionQueueID,
		 A1.EnterpriseName,
		 A2.EnterpriseName,
		 A3.EnterpriseName,
		 A4.EnterpriseName,
		 A5.EnterpriseName,
		 A6.EnterpriseName,
         A7.EnterpriseName,
         A8.EnterpriseName,
         A9.EnterpriseName,
         A10.EnterpriseName,
		 PQRT.DateTime,
		 CONVERT(char(10),PQRT.DateTime,101),
		 PQRT.LongestCallInQ 
ORDER BY Precision_Queue.EnterpriseName,   
		 Precision_Queue.PrecisionQueueID,
		 PQRT.DateTime

This gave me back results.

 

SELECT
DateTime,
PrecisionQueue = Precision_Queue.EnterpriseName,
PrecisionQueueID = Precision_Queue.PrecisionQueueID,
CallsAnsweredTo5 = SUM(ISNULL(PQRT.CallsAnsweredTo5, 0)),
CallsAband = SUM(ISNULL(PQRT.CallsAbandQTo5, 0)) + SUM(ISNULL(PQRT.CallsAbandToAgentTo5, 0)), 
Total_Volume  = SUM(ISNULL(PQRT.CallsOfferedTo5, 0)),
PercentAbandon =  case when SUM(ISNULL(PQRT.CallsOfferedTo5, 0)) = 0 then 0 
else (SUM(ISNULL(PQRT.CallsAbandQTo5, 0)) + SUM(ISNULL(PQRT.CallsAbandToAgentTo5, 0))) * 100.0/ SUM(ISNULL(PQRT.CallsOfferedTo5, 0)) end,
CallsQNow = SUM(ISNULL(PQRT.CallsQNow, 0))
FROM Precision_Queue (nolock),
         Media_Routing_Domain (nolock), 
	 Precision_Q_Real_Time PQRT (nolock)
  LEFT OUTER JOIN Attribute A1 (nolock) on PQRT.AttributeID1 = A1.AttributeID
  LEFT OUTER JOIN Attribute A2 (nolock) on PQRT.AttributeID2 = A2.AttributeID
  LEFT OUTER JOIN Attribute A3 (nolock) on PQRT.AttributeID3 = A3.AttributeID
  LEFT OUTER JOIN Attribute A4 (nolock) on PQRT.AttributeID4 = A4.AttributeID
  LEFT OUTER JOIN Attribute A5 (nolock) on PQRT.AttributeID5 = A5.AttributeID
  LEFT OUTER JOIN Attribute A6 (nolock) on PQRT.AttributeID6 = A6.AttributeID
  LEFT OUTER JOIN Attribute A7 (nolock) on PQRT.AttributeID7 = A7.AttributeID
  LEFT OUTER JOIN Attribute A8 (nolock) on PQRT.AttributeID8 = A8.AttributeID
  LEFT OUTER JOIN Attribute A9 (nolock) on PQRT.AttributeID9 = A9.AttributeID
  LEFT OUTER JOIN Attribute A10 (nolock) on PQRT.AttributeID10 = A10.AttributeID

WHERE DateTime BETWEEN '06-01-2022 00:00:00' AND '06-08-2022 00:00:00'
AND Precision_Queue.PrecisionQueueID IN (5055, 5056, 5057, 5061, 5069, 5070, 5071, 5080, 5081, 5082, 5083, 5084, 5085, 5114, 5115, 5116, 5117, 5118, 5122, 5123, 5124, 5140, 5154)
AND  (PQRT.PrecisionQueueID = Precision_Queue.PrecisionQueueID)
AND  ( Media_Routing_Domain.MRDomainID = Precision_Queue.MRDomainID) 

GROUP BY Media_Routing_Domain.EnterpriseName,
		Media_Routing_Domain.MRDomainID,
        Precision_Queue.EnterpriseName,   
		 Precision_Queue.PrecisionQueueID,
		 A1.EnterpriseName,
		 A2.EnterpriseName,
		 A3.EnterpriseName,
		 A4.EnterpriseName,
		 A5.EnterpriseName,
		 A6.EnterpriseName,
         A7.EnterpriseName,
         A8.EnterpriseName,
         A9.EnterpriseName,
         A10.EnterpriseName,
		 PQRT.DateTime,
		 CONVERT(char(10),PQRT.DateTime,101),
		 PQRT.LongestCallInQ 
ORDER BY Precision_Queue.EnterpriseName,   
		 Precision_Queue.PrecisionQueueID,
		 PQRT.DateTime

Right, it does. 

 

But it doesn't give me results for the date range that i have for DateTime...

 

It seems to be giving me the same data for the data range of 6/7 - 6/8 instead of 6/1-6/8

Because that data is no longer available in the DB. Take a look at a historical report instead and that will work better, but you're not going to get the exact same stats.

 

david

Also make sure you're running it against the right data source. It doesn't seem right that you'd only have a few days worth of data in your database. Are you running it in SQL against the Central Controller/Logger vs. running it against your HDS? The data should be there, though, unless you've purged it for some reason.

SubnetZero1
Level 1
Level 1

Thanks all for your responses.

 

I ended up using a query from CUIC and modified it. I hope this helps someone in the future with similar issue.

 

        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=S.DateTime,
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(R.CallsHandled),
--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(R.CallsAnswered),
--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(R.RedirectNoAnsCalls),
--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(ISNULL(R.RouterError,0)),  
/*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),
--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(ISNULL(R.CallsAbandQ,0) + ISNULL(R.CallsAbandToAgent,0)),
--pickRequests = sum(isnull(R.PickRequests,0)),
--pickErrors = sum(isnull(R.PickErrors,0)),
--pullRequests = sum(isnull(R.PullRequests,0)),
TotalComplete=SUM(ISNULL(R.CallsHandled,0))+SUM(ISNULL(R.RedirectNoAnsCalls,0))+SUM(ISNULL(R.CallsAbandQ,0))+SUM(ISNULL(R.RouterError,0))+SUM(ISNULL(R.CallsAbandToAgent,0))
--pullErrors = sum(isnull(R.PullErrors,0))
FROM
Router_Queue_Interval R (nolock)
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,
(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)),
RI=MAX(ISNULL(ReportingInterval,0))
from Skill_Group_Interval (nolock)
group by PrecisionQueueID, DateTime) S, 
Precision_Queue PQ (nolock),
Media_Routing_Domain  (nolock)  
WHERE PQ.PrecisionQueueID IN (5055, 5056, 5057, 5061, 5069, 5070, 5071, 5080, 5081, 5082, 5083, 5084, 5085, 5114, 5115, 5116, 5117, 5118, 5122, 5123, 5124, 5140, 5154) 
--and (((S.DateTime between '2022-05-31 22:00:00' and '2022-05-31 23:59:59' and (DATEPART(dw, S.DateTime) in(1,2,3,4,5,6,7))) or (S.DateTime between '2022-06-08 00:00:00' and '2022-06-08 21:59:59' and (DATEPART(dw, S.DateTime) in(2,3,4,5,6,7,1))) or (S.DateTime between '2022-06-01 00:00:00' and '2022-06-07 23:59:59' and ((convert([char], S.DateTime, 108) between '22:00:00' and '23:59:59' and (DATEPART(dw, S.DateTime) in(1,2,3,4,5,6,7))) or (convert([char], S.DateTime, 108) between '00:00:00' and '21:59:59' and (DATEPART(dw, S.DateTime) in(2,3,4,5,6,7,1))))))) 
and S.DateTime BETWEEN '06-28-2022 00:00:00' AND '06-29-2022 00:00:00'
and PQ.PrecisionQueueID=S.PrecisionQueueID 
and R.DateTime=S.DateTime 
and PQ.PrecisionQueueID=R.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
Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: