04-04-2012 02:37 AM - edited 03-14-2019 09:39 AM
I would like to have an report for skillgroup as the customer requirement refer to the
perskg22 - icm peripheral skill group task summary daily.
I do have the skill group interval, but i could not able generate the report for consolidated output. Even i tried from web view query it is providing the half hour report not consolidated.
Please help me to have the template or advise me how to generate.
with Regards,
Manivannan
04-04-2012 02:45 AM
Hi,
is that a custom report?
If yes, why don't you try a GROUP BY clause in SQL?
G.
04-04-2012 03:27 AM
This was the normal report in webview but now we dont have anything like this in cuic templates,
of course it is custom report now, i do have copied the sql query from another working report in where as group by define as follows
Group By
Media_Routing_Domain.EnterpriseName,
Skill_Group.EnterpriseName,
SGHH.DateTime
with Regards,
Manivannan
04-04-2012 04:00 AM
Hi,
you see, if you group by SGHH.DateTime while SGHH.DateTime is a half hour DateTime type timestamp, no wonder the report itself contains a table where each row is a half hour interval.
Can you just do this:
Within the SELECT list:
instead of
SGHH.DateTime
use
CAST(FLOOR(CAST(SGHH.DateTime AS FLOAT)) AS DATETIME) AS [DateTime],
And within the GROUP BY clause just use:
GROUP BY
Media_Routing_Domain.EnterpriseName,
Skill_Group.EnterpriseName,
CAST(FLOOR(CAST(SGHH.DateTime AS FLOAT)) AS DATETIME)
G.
04-04-2012 05:21 AM
Hi Gergely,
Thanks for the input, i have tried to replace the Interval-SGHH.DateTime with suggest CAST command, but it showing always some error, could you help me modify the below Query it would be grateful to you.
SELECT
Interval = SGHH.DateTime,
Datepart(yy, SGHH.DateTime) as Year, Datepart(mm, SGHH.DateTime) as Month,
Datepart(ww, SGHH.DateTime) as Week,
Datepart(dy, SGHH.DateTime) as DOY,
Datepart(dw, SGHH.DateTime) as DOW,
CONVERT(char(10),SGHH.DateTime,101) as DATE,
FullName = Skill_Group.EnterpriseName,
SkillGroupSkillID = Skill_Group.SkillTargetID,
CallbackMessages = SUM(ISNULL(SGHH.CallbackMessages, 0)),
CallbackMessagesTime = SUM(ISNULL(SGHH.CallbackMessagesTime, 0)),
AvgHandledCallsTalkTime = SUM(ISNULL(SGHH.AvgHandledCallsTalkTime, 0)),
HoldTime = SUM(ISNULL(SGHH.HoldTime, 0)),
HandledCallsTalkTime = SUM(ISNULL(SGHH.HandledCallsTalkTime, 0)),
InternalCalls = SUM(ISNULL(SGHH.InternalCalls, 0)),
InternalCallsTime = SUM(ISNULL(SGHH.InternalCallsTime, 0)),
CallsHandled = SUM(ISNULL(SGHH.CallsHandled, 0)),
SupervAssistCalls= SUM(ISNULL(SGHH.SupervAssistCalls, 0)),
AvgHandledCallsTime = SUM(ISNULL(SGHH.AvgHandledCallsTime, 0)),
SupervAssistCallsTime = SUM(ISNULL(SGHH.SupervAssistCallsTime, 0)),
HandledCallsTime = SUM(ISNULL(SGHH.HandledCallsTime, 0)),
PercentUtilization = SUM(ISNULL(SGHH.PercentUtilization, 0)),
AgentOutCallsTime = SUM(ISNULL(SGHH.AgentOutCallsTime, 0)),
TalkInTime = SUM(ISNULL(SGHH.TalkInTime, 0)),
LoggedOnTime = SUM(ISNULL(SGHH.LoggedOnTime, 0)),
ExternalOut = SUM(ISNULL(SGHH.AgentOutCalls, 0)),
TalkOutTime = SUM(ISNULL(SGHH.TalkOutTime, 0)),
TalkOtherTime = SUM(ISNULL(SGHH.TalkOtherTime, 0)),
AvailTime = SUM(ISNULL(SGHH.AvailTime, 0)),
NotReadyTime = SUM(ISNULL(SGHH.NotReadyTime, 0)),
TransferInCalls = SUM(ISNULL(SGHH.TransferInCalls, 0)),
TalkTime = SUM(ISNULL(SGHH.TalkTime, 0)),
TransferInCallsTime = SUM(ISNULL(SGHH.TransferInCallsTime, 0)),
WorkReadyTime = SUM(ISNULL(SGHH.WorkReadyTime, 0)),
TransferOutCalls = SUM(ISNULL(SGHH.TransferOutCalls, 0)),
WorkNotReadyTime = SUM(ISNULL(SGHH.WorkNotReadyTime, 0)),
RecoveryDay = SUM(ISNULL(SGHH.RecoveryDay, 0)), BusyOtherTime = SUM(ISNULL(SGHH.BusyOtherTime, 0)),
CallsAnswered = SUM(ISNULL(SGHH.CallsAnswered, 0)),
ReservedStateTime = SUM(ISNULL(SGHH.ReservedStateTime, 0)),
AnswerWaitTime = SUM(ISNULL(SGHH.AnswerWaitTime, 0)),
AbandonRingCalls = SUM(ISNULL(SGHH.AbandonRingCalls, 0)),
AbandonRingTime = SUM(ISNULL(SGHH.AbandonRingTime, 0)),
AbandonHoldCalls = SUM(ISNULL(SGHH.AbandonHoldCalls, 0)),
AgentOutCallsTalkTime = SUM(ISNULL(SGHH.AgentOutCallsTalkTime, 0)),
AgentOutCallsOnHold = SUM(ISNULL(SGHH.AgentOutCallsOnHold, 0)),
AgentOutCallsOnHoldTime = SUM(ISNULL(SGHH.AgentOutCallsOnHoldTime, 0)),
AgentTerminatedCalls = SUM(ISNULL(SGHH.AgentTerminatedCalls, 0)),
ConsultativeCalls = SUM(ISNULL(SGHH.ConsultativeCalls, 0)),
ConsultativeCallsTime = SUM(ISNULL(SGHH.ConsultativeCallsTime, 0)),
ConferencedInCalls = SUM(ISNULL(SGHH.ConferencedInCalls, 0)),
ConferencedInCallsTime = SUM(ISNULL(SGHH.ConferencedInCallsTime, 0)),
ConferencedOutCalls = SUM(ISNULL(SGHH.ConferencedOutCalls, 0)),
ConferencedOutCallsTime = SUM(ISNULL(SGHH.ConferencedOutCallsTime, 0)),
IncomingCallsOnHoldTime = SUM(ISNULL(SGHH.IncomingCallsOnHoldTime, 0)),
IncomingCallsOnHold = SUM(ISNULL(SGHH.IncomingCallsOnHold, 0)),
InternalCallsOnHoldTime =SUM(ISNULL(SGHH.InternalCallsOnHoldTime, 0)),
InternalCallsOnHold = SUM(ISNULL(SGHH.InternalCallsOnHold, 0)),
InternalCallsRcvdTime = SUM(ISNULL(SGHH.InternalCallsRcvdTime, 0)),
InternalCallsRcvd = SUM(ISNULL(SGHH.InternalCallsRcvd, 0)),
RedirectNoAnsCalls = SUM(ISNULL(SGHH.RedirectNoAnsCalls, 0)),
RedirectNoAnsCallsTime = SUM(ISNULL(SGHH.RedirectNoAnsCallsTime, 0)),
ShortCalls = SUM(ISNULL(SGHH.ShortCalls, 0)),
RouterCallsAbandQ = SUM(ISNULL(SGHH.RouterCallsAbandQ, 0)),
RouterQueueCalls = SUM(ISNULL(SGHH.RouterQueueCalls, 0)),
AutoOutCalls = SUM(ISNULL(SGHH.AutoOutCalls, 0)),
AutoOutCallsTime = SUM(ISNULL(SGHH.AutoOutCallsTime, 0)),
AutoOutCallsTalkTime = SUM(ISNULL(SGHH.AutoOutCallsTalkTime, 0)),
AutoOutCallsOnHold = SUM(ISNULL(SGHH.AutoOutCallsOnHold, 0)),
AutoOutCallsOnHoldTime = SUM(ISNULL(SGHH.AutoOutCallsOnHoldTime, 0)),
PreviewCalls = SUM(ISNULL(SGHH.PreviewCalls, 0)),
PreviewCallsTime = SUM(ISNULL(SGHH.PreviewCallsTime, 0)),
PreviewCallsTalkTime = SUM(ISNULL(SGHH.PreviewCallsTalkTime, 0)),
PreviewCallsOnHold = SUM(ISNULL(SGHH.PreviewCallsOnHold, 0)),
PreviewCallsOnHoldTime = SUM(ISNULL(SGHH.PreviewCallsOnHoldTime, 0)),
ReserveCalls = SUM(ISNULL(SGHH.ReserveCalls, 0)),
ReserveCallsTime = SUM(ISNULL(SGHH.ReserveCallsTime, 0)),
ReserveCallsTalkTime = SUM(ISNULL(SGHH.ReserveCallsTalkTime, 0)),
ReserveCallsOnHold = SUM(ISNULL(SGHH.ReserveCallsOnHold, 0)),
ReserveCallsOnHoldTime = SUM(ISNULL(SGHH.ReserveCallsOnHoldTime, 0)),
TalkAutoOutTime = SUM(ISNULL(SGHH.TalkAutoOutTime, 0)),
TalkPreviewTime = SUM(ISNULL(SGHH.TalkPreviewTime, 0)),
TalkReserveTime = SUM(ISNULL(SGHH.TalkReserveTime, 0)),
BargeInCalls = SUM(ISNULL(SGHH.BargeInCalls, 0)),
InterceptCalls = SUM(ISNULL(SGHH.InterceptCalls, 0)),
MonitorCalls = SUM(ISNULL(SGHH.MonitorCalls, 0)),
WhisperCalls = SUM(ISNULL(SGHH.WhisperCalls, 0)),
EmergencyAssists = SUM(ISNULL(SGHH.EmergencyAssists, 0)),
CallsOffered = SUM(ISNULL(SGHH.CallsHandled, 0)) + SUM(ISNULL(SGHH.RouterCallsAbandQ, 0)) + SUM(ISNULL(SGHH.AbandonRingCalls, 0))+ SUM(ISNULL(SGHH.RedirectNoAnsCalls, 0)),
CallsQueued = SUM(ISNULL(SGHH.RouterQueueCalls, 0)),
InterruptedTime = SUM(ISNULL(SGHH.InterruptedTime, 0)),
RecoveryKey = SGHH.RecoveryKey,
TimeZone = SGHH.TimeZone,
RouterCallsOffered=SUM(ISNULL(SGHH.RouterCallsOffered, 0)),
RouterCallsAgentAbandons = SUM(ISNULL(SGHH.RouterCallsAbandToAgent, 0)),
RouterCallsDequeued = SUM(ISNULL(SGHH.RouterCallsDequeued, 0)),
RouterError=SUM(ISNULL(SGHH.RouterError, 0)),
ServiceLevel=SUM(ISNULL(SGHH.ServiceLevel, 0)),
ServiceLevelCalls=SUM(ISNULL(SGHH.ServiceLevelCalls, 0)),
ServiceLevelCallsAband= SUM(ISNULL(SGHH.ServiceLevelCallsAband, 0)),
ServiceLevelCallsDequeue=SUM(ISNULL(SGHH.ServiceLevelCallsDequeue, 0)),
ServiceLevelError= SUM(ISNULL(SGHH.ServiceLevelError, 0)),
ServiceLevelRONA=SUM(ISNULL(SGHH.ServiceLevelRONA, 0)),
ServiceLevelCallsOffered= SUM(ISNULL(SGHH.ServiceLevelCallsOffered,0)),
NetConsultativeCalls = sum(isnull(SGHH.NetConsultativeCalls, 0)),
NetConsultativeCallsTime=SUM(ISNULL(SGHH.NetConsultativeCallsTime, 0)),
NetConferencedOutCalls=sum(isnull( SGHH.NetConferencedOutCalls, 0)),
NetConfOutCallsTime=SUM(ISNULL(SGHH.NetConfOutCallsTime, 0)),
NetTransferredOutCalls = sum(isnull(SGHH.NetTransferOutCalls, 0)),
DbDateTime = SGHH.DbDateTime,
fte_AgentsLogonTotal=SUM(ISNULL(SGHH.LoggedOnTime,0)) * 1.0 / 1800,
fte_AgentsNotReady=SUM(ISNULL(SGHH.NotReadyTime,0)) * 1.0 / 1800,
fte_AgentsNotActive=SUM(ISNULL(SGHH.AvailTime,0)) * 1.0 / 1800,
fte_AgentsActive=SUM(ISNULL(SGHH.TalkTime,0)) * 1.0 / 1800,
fte_AgentsWrapup=SUM(ISNULL(SGHH.WorkReadyTime, 0)+ISNULL(SGHH.WorkNotReadyTime, 0)) * 1.0 / 1800,
fte_AgentsOther=SUM(ISNULL(SGHH.BusyOtherTime,0)) * 1.0 / 1800,
fte_AgentsHold=SUM(ISNULL(SGHH.HoldTime,0)) * 1.0 / 1800,
fte_AgentsReserved=SUM(ISNULL(SGHH.ReservedStateTime,0)) * 1.0 / 1800 ,
ast_PercentNotActiveTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.AvailTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentActiveTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.TalkTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentHoldTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.HoldTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentWrapTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE (SUM(ISNULL(SGHH.WorkNotReadyTime + SGHH.WorkReadyTime, 0))) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentReservedTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.ReservedStateTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentNotReadyTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.NotReadyTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentUtilization=
CASE WHEN SUM(ISNULL(SGHH.TalkTime, 0)) = 0 THEN 0
ELSE ISNULL((SUM(ISNULL(SGHH.TalkInTime, 0)) +
SUM(ISNULL(SGHH.TalkOutTime, 0)) +
SUM(ISNULL(SGHH.TalkOtherTime, 0)) +
SUM(ISNULL(SGHH.WorkReadyTime, 0)) +
SUM(ISNULL(SGHH.WorkNotReadyTime, 0))) * 1.0 /
(SUM(ISNULL(SGHH.LoggedOnTime, 0)) - SUM(ISNULL(SGHH.NotReadyTime, 0))), 0)
END,
asa= ISNULL(SUM(ISNULL(SGHH.AnswerWaitTime,0)) * 1.0 / SUM(ISNULL(SGHH.CallsAnswered,0)),0),
CompletedTasks_AHT = (CASE WHEN SUM(ISNULL(SGHH.CallsHandled,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.HandledCallsTime,0)) / SUM(ISNULL(SGHH.CallsHandled,0)) END),
CompletedTasks_AvgActiveTime=ISNULL((SUM(ISNULL(SGHH.HandledCallsTalkTime, 0)) / SUM(ISNULL(SGHH.CallsHandled, 0))), 0),
CompletedTasks_AvgWrapTime=ISNULL(((SUM(ISNULL(SGHH.HandledCallsTime, 0)) - SUM(ISNULL(SGHH.HandledCallsTalkTime,0)) - SUM(ISNULL(SGHH.IncomingCallsOnHoldTime, 0))) / SUM(ISNULL(SGHH.CallsHandled,0))), 0),
ast_ActiveTime = SUM(ISNULL(SGHH.TalkTime, 0)),
TotalQueued = SUM(ISNULL(SGHH.RouterQueueCalls, 0)) + SUM(ISNULL(SGHH.CallsQueued, 0)),
ast_PerBusyOtherTime = (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.BusyOtherTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
Media= Media_Routing_Domain.EnterpriseName,
AbandCalls = SUM(ISNULL(SGHH.AbandonRingCalls, 0)) + SUM(ISNULL(SGHH.RouterCallsAbandQ, 0)),
RouterMaxCallsQueued=sum(SGHH.RouterMaxCallsQueued),
RouterMaxCallWaitTime=sum(SGHH.RouterMaxCallWaitTime)
FROM Skill_Group (nolock), Skill_Group_Interval SGHH (nolock), Media_Routing_Domain (nolock)
WHERE ( Skill_Group.SkillTargetID = SGHH.SkillTargetID ) and
( Skill_Group.MRDomainID = Media_Routing_Domain.MRDomainID )
GROUP BY
Skill_Group.EnterpriseName,
Skill_Group.SkillTargetID,
Media_Routing_Domain.EnterpriseName,
SGHH.DateTime,
Datepart(yy, SGHH.DateTime),
Datepart(mm, SGHH.DateTime),
Datepart(ww, SGHH.DateTime),
Datepart(dy, SGHH.DateTime),
Datepart(dw, SGHH.DateTime),
CONVERT(char(10),SGHH.DateTime,101),
SGHH.RecoveryKey,
SGHH.TimeZone,
SGHH.DbDateTime
ORDER BY Media_Routing_Domain.EnterpriseName ,Skill_Group.EnterpriseName, SGHH.DateTime
SELECT
Interval = SGHH.DateTime,
Datepart(yy, SGHH.DateTime) as Year, Datepart(mm, SGHH.DateTime) as Month,
Datepart(ww, SGHH.DateTime) as Week,
Datepart(dy, SGHH.DateTime) as DOY,
Datepart(dw, SGHH.DateTime) as DOW,
CONVERT(char(10),SGHH.DateTime,101) as DATE,
FullName = Skill_Group.EnterpriseName,
SkillGroupSkillID = Skill_Group.SkillTargetID,
CallbackMessages = SUM(ISNULL(SGHH.CallbackMessages, 0)),
CallbackMessagesTime = SUM(ISNULL(SGHH.CallbackMessagesTime, 0)),
AvgHandledCallsTalkTime = SUM(ISNULL(SGHH.AvgHandledCallsTalkTime, 0)),
HoldTime = SUM(ISNULL(SGHH.HoldTime, 0)),
HandledCallsTalkTime = SUM(ISNULL(SGHH.HandledCallsTalkTime, 0)),
InternalCalls = SUM(ISNULL(SGHH.InternalCalls, 0)),
InternalCallsTime = SUM(ISNULL(SGHH.InternalCallsTime, 0)),
CallsHandled = SUM(ISNULL(SGHH.CallsHandled, 0)),
SupervAssistCalls= SUM(ISNULL(SGHH.SupervAssistCalls, 0)),
AvgHandledCallsTime = SUM(ISNULL(SGHH.AvgHandledCallsTime, 0)),
SupervAssistCallsTime = SUM(ISNULL(SGHH.SupervAssistCallsTime, 0)),
HandledCallsTime = SUM(ISNULL(SGHH.HandledCallsTime, 0)),
PercentUtilization = SUM(ISNULL(SGHH.PercentUtilization, 0)),
AgentOutCallsTime = SUM(ISNULL(SGHH.AgentOutCallsTime, 0)),
TalkInTime = SUM(ISNULL(SGHH.TalkInTime, 0)),
LoggedOnTime = SUM(ISNULL(SGHH.LoggedOnTime, 0)),
ExternalOut = SUM(ISNULL(SGHH.AgentOutCalls, 0)),
TalkOutTime = SUM(ISNULL(SGHH.TalkOutTime, 0)),
TalkOtherTime = SUM(ISNULL(SGHH.TalkOtherTime, 0)),
AvailTime = SUM(ISNULL(SGHH.AvailTime, 0)),
NotReadyTime = SUM(ISNULL(SGHH.NotReadyTime, 0)),
TransferInCalls = SUM(ISNULL(SGHH.TransferInCalls, 0)),
TalkTime = SUM(ISNULL(SGHH.TalkTime, 0)),
TransferInCallsTime = SUM(ISNULL(SGHH.TransferInCallsTime, 0)),
WorkReadyTime = SUM(ISNULL(SGHH.WorkReadyTime, 0)),
TransferOutCalls = SUM(ISNULL(SGHH.TransferOutCalls, 0)),
WorkNotReadyTime = SUM(ISNULL(SGHH.WorkNotReadyTime, 0)),
RecoveryDay = SUM(ISNULL(SGHH.RecoveryDay, 0)), BusyOtherTime = SUM(ISNULL(SGHH.BusyOtherTime, 0)),
CallsAnswered = SUM(ISNULL(SGHH.CallsAnswered, 0)),
ReservedStateTime = SUM(ISNULL(SGHH.ReservedStateTime, 0)),
AnswerWaitTime = SUM(ISNULL(SGHH.AnswerWaitTime, 0)),
AbandonRingCalls = SUM(ISNULL(SGHH.AbandonRingCalls, 0)),
AbandonRingTime = SUM(ISNULL(SGHH.AbandonRingTime, 0)),
AbandonHoldCalls = SUM(ISNULL(SGHH.AbandonHoldCalls, 0)),
AgentOutCallsTalkTime = SUM(ISNULL(SGHH.AgentOutCallsTalkTime, 0)),
AgentOutCallsOnHold = SUM(ISNULL(SGHH.AgentOutCallsOnHold, 0)),
AgentOutCallsOnHoldTime = SUM(ISNULL(SGHH.AgentOutCallsOnHoldTime, 0)),
AgentTerminatedCalls = SUM(ISNULL(SGHH.AgentTerminatedCalls, 0)),
ConsultativeCalls = SUM(ISNULL(SGHH.ConsultativeCalls, 0)),
ConsultativeCallsTime = SUM(ISNULL(SGHH.ConsultativeCallsTime, 0)),
ConferencedInCalls = SUM(ISNULL(SGHH.ConferencedInCalls, 0)),
ConferencedInCallsTime = SUM(ISNULL(SGHH.ConferencedInCallsTime, 0)),
ConferencedOutCalls = SUM(ISNULL(SGHH.ConferencedOutCalls, 0)),
ConferencedOutCallsTime = SUM(ISNULL(SGHH.ConferencedOutCallsTime, 0)),
IncomingCallsOnHoldTime = SUM(ISNULL(SGHH.IncomingCallsOnHoldTime, 0)),
IncomingCallsOnHold = SUM(ISNULL(SGHH.IncomingCallsOnHold, 0)),
InternalCallsOnHoldTime =SUM(ISNULL(SGHH.InternalCallsOnHoldTime, 0)),
InternalCallsOnHold = SUM(ISNULL(SGHH.InternalCallsOnHold, 0)),
InternalCallsRcvdTime = SUM(ISNULL(SGHH.InternalCallsRcvdTime, 0)),
InternalCallsRcvd = SUM(ISNULL(SGHH.InternalCallsRcvd, 0)),
RedirectNoAnsCalls = SUM(ISNULL(SGHH.RedirectNoAnsCalls, 0)),
RedirectNoAnsCallsTime = SUM(ISNULL(SGHH.RedirectNoAnsCallsTime, 0)),
ShortCalls = SUM(ISNULL(SGHH.ShortCalls, 0)),
RouterCallsAbandQ = SUM(ISNULL(SGHH.RouterCallsAbandQ, 0)),
RouterQueueCalls = SUM(ISNULL(SGHH.RouterQueueCalls, 0)),
AutoOutCalls = SUM(ISNULL(SGHH.AutoOutCalls, 0)),
AutoOutCallsTime = SUM(ISNULL(SGHH.AutoOutCallsTime, 0)),
AutoOutCallsTalkTime = SUM(ISNULL(SGHH.AutoOutCallsTalkTime, 0)),
AutoOutCallsOnHold = SUM(ISNULL(SGHH.AutoOutCallsOnHold, 0)),
AutoOutCallsOnHoldTime = SUM(ISNULL(SGHH.AutoOutCallsOnHoldTime, 0)),
PreviewCalls = SUM(ISNULL(SGHH.PreviewCalls, 0)),
PreviewCallsTime = SUM(ISNULL(SGHH.PreviewCallsTime, 0)),
PreviewCallsTalkTime = SUM(ISNULL(SGHH.PreviewCallsTalkTime, 0)),
PreviewCallsOnHold = SUM(ISNULL(SGHH.PreviewCallsOnHold, 0)),
PreviewCallsOnHoldTime = SUM(ISNULL(SGHH.PreviewCallsOnHoldTime, 0)),
ReserveCalls = SUM(ISNULL(SGHH.ReserveCalls, 0)),
ReserveCallsTime = SUM(ISNULL(SGHH.ReserveCallsTime, 0)),
ReserveCallsTalkTime = SUM(ISNULL(SGHH.ReserveCallsTalkTime, 0)),
ReserveCallsOnHold = SUM(ISNULL(SGHH.ReserveCallsOnHold, 0)),
ReserveCallsOnHoldTime = SUM(ISNULL(SGHH.ReserveCallsOnHoldTime, 0)),
TalkAutoOutTime = SUM(ISNULL(SGHH.TalkAutoOutTime, 0)),
TalkPreviewTime = SUM(ISNULL(SGHH.TalkPreviewTime, 0)),
TalkReserveTime = SUM(ISNULL(SGHH.TalkReserveTime, 0)),
BargeInCalls = SUM(ISNULL(SGHH.BargeInCalls, 0)),
InterceptCalls = SUM(ISNULL(SGHH.InterceptCalls, 0)),
MonitorCalls = SUM(ISNULL(SGHH.MonitorCalls, 0)),
WhisperCalls = SUM(ISNULL(SGHH.WhisperCalls, 0)),
EmergencyAssists = SUM(ISNULL(SGHH.EmergencyAssists, 0)),
CallsOffered = SUM(ISNULL(SGHH.CallsHandled, 0)) + SUM(ISNULL(SGHH.RouterCallsAbandQ, 0)) + SUM(ISNULL(SGHH.AbandonRingCalls, 0))+ SUM(ISNULL(SGHH.RedirectNoAnsCalls, 0)),
CallsQueued = SUM(ISNULL(SGHH.RouterQueueCalls, 0)),
InterruptedTime = SUM(ISNULL(SGHH.InterruptedTime, 0)),
RecoveryKey = SGHH.RecoveryKey,
TimeZone = SGHH.TimeZone,
RouterCallsOffered=SUM(ISNULL(SGHH.RouterCallsOffered, 0)),
RouterCallsAgentAbandons = SUM(ISNULL(SGHH.RouterCallsAbandToAgent, 0)),
RouterCallsDequeued = SUM(ISNULL(SGHH.RouterCallsDequeued, 0)),
RouterError=SUM(ISNULL(SGHH.RouterError, 0)),
ServiceLevel=SUM(ISNULL(SGHH.ServiceLevel, 0)),
ServiceLevelCalls=SUM(ISNULL(SGHH.ServiceLevelCalls, 0)),
ServiceLevelCallsAband= SUM(ISNULL(SGHH.ServiceLevelCallsAband, 0)),
ServiceLevelCallsDequeue=SUM(ISNULL(SGHH.ServiceLevelCallsDequeue, 0)),
ServiceLevelError= SUM(ISNULL(SGHH.ServiceLevelError, 0)),
ServiceLevelRONA=SUM(ISNULL(SGHH.ServiceLevelRONA, 0)),
ServiceLevelCallsOffered= SUM(ISNULL(SGHH.ServiceLevelCallsOffered,0)),
NetConsultativeCalls = sum(isnull(SGHH.NetConsultativeCalls, 0)),
NetConsultativeCallsTime=SUM(ISNULL(SGHH.NetConsultativeCallsTime, 0)),
NetConferencedOutCalls=sum(isnull( SGHH.NetConferencedOutCalls, 0)),
NetConfOutCallsTime=SUM(ISNULL(SGHH.NetConfOutCallsTime, 0)),
NetTransferredOutCalls = sum(isnull(SGHH.NetTransferOutCalls, 0)),
DbDateTime = SGHH.DbDateTime,
fte_AgentsLogonTotal=SUM(ISNULL(SGHH.LoggedOnTime,0)) * 1.0 / 1800,
fte_AgentsNotReady=SUM(ISNULL(SGHH.NotReadyTime,0)) * 1.0 / 1800,
fte_AgentsNotActive=SUM(ISNULL(SGHH.AvailTime,0)) * 1.0 / 1800,
fte_AgentsActive=SUM(ISNULL(SGHH.TalkTime,0)) * 1.0 / 1800,
fte_AgentsWrapup=SUM(ISNULL(SGHH.WorkReadyTime, 0)+ISNULL(SGHH.WorkNotReadyTime, 0)) * 1.0 / 1800,
fte_AgentsOther=SUM(ISNULL(SGHH.BusyOtherTime,0)) * 1.0 / 1800,
fte_AgentsHold=SUM(ISNULL(SGHH.HoldTime,0)) * 1.0 / 1800,
fte_AgentsReserved=SUM(ISNULL(SGHH.ReservedStateTime,0)) * 1.0 / 1800 ,
ast_PercentNotActiveTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.AvailTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentActiveTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.TalkTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentHoldTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.HoldTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentWrapTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE (SUM(ISNULL(SGHH.WorkNotReadyTime + SGHH.WorkReadyTime, 0))) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentReservedTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.ReservedStateTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentNotReadyTime= (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.NotReadyTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
ast_PercentUtilization=
CASE WHEN SUM(ISNULL(SGHH.TalkTime, 0)) = 0 THEN 0
ELSE ISNULL((SUM(ISNULL(SGHH.TalkInTime, 0)) +
SUM(ISNULL(SGHH.TalkOutTime, 0)) +
SUM(ISNULL(SGHH.TalkOtherTime, 0)) +
SUM(ISNULL(SGHH.WorkReadyTime, 0)) +
SUM(ISNULL(SGHH.WorkNotReadyTime, 0))) * 1.0 /
(SUM(ISNULL(SGHH.LoggedOnTime, 0)) - SUM(ISNULL(SGHH.NotReadyTime, 0))), 0)
END,
asa= ISNULL(SUM(ISNULL(SGHH.AnswerWaitTime,0)) * 1.0 / SUM(ISNULL(SGHH.CallsAnswered,0)),0),
CompletedTasks_AHT = (CASE WHEN SUM(ISNULL(SGHH.CallsHandled,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.HandledCallsTime,0)) / SUM(ISNULL(SGHH.CallsHandled,0)) END),
CompletedTasks_AvgActiveTime=ISNULL((SUM(ISNULL(SGHH.HandledCallsTalkTime, 0)) / SUM(ISNULL(SGHH.CallsHandled, 0))), 0),
CompletedTasks_AvgWrapTime=ISNULL(((SUM(ISNULL(SGHH.HandledCallsTime, 0)) - SUM(ISNULL(SGHH.HandledCallsTalkTime,0)) - SUM(ISNULL(SGHH.IncomingCallsOnHoldTime, 0))) / SUM(ISNULL(SGHH.CallsHandled,0))), 0),
ast_ActiveTime = SUM(ISNULL(SGHH.TalkTime, 0)),
TotalQueued = SUM(ISNULL(SGHH.RouterQueueCalls, 0)) + SUM(ISNULL(SGHH.CallsQueued, 0)),
ast_PerBusyOtherTime = (CASE WHEN SUM(ISNULL(SGHH.LoggedOnTime,0)) = 0 THEN 0
ELSE SUM(ISNULL(SGHH.BusyOtherTime,0)) * 1.0 / SUM(ISNULL(SGHH.LoggedOnTime,0)) END),
Media= Media_Routing_Domain.EnterpriseName,
AbandCalls = SUM(ISNULL(SGHH.AbandonRingCalls, 0)) + SUM(ISNULL(SGHH.RouterCallsAbandQ, 0)),
RouterMaxCallsQueued=sum(SGHH.RouterMaxCallsQueued),
RouterMaxCallWaitTime=sum(SGHH.RouterMaxCallWaitTime)
FROM Skill_Group (nolock), Skill_Group_Interval SGHH (nolock), Media_Routing_Domain (nolock)
WHERE ( Skill_Group.SkillTargetID = SGHH.SkillTargetID ) and
( Skill_Group.MRDomainID = Media_Routing_Domain.MRDomainID )
GROUP BY
Skill_Group.EnterpriseName,
Skill_Group.SkillTargetID,
Media_Routing_Domain.EnterpriseName,
SGHH.DateTime,
Datepart(yy, SGHH.DateTime),
Datepart(mm, SGHH.DateTime),
Datepart(ww, SGHH.DateTime),
Datepart(dy, SGHH.DateTime),
Datepart(dw, SGHH.DateTime),
CONVERT(char(10),SGHH.DateTime,101),
SGHH.RecoveryKey,
SGHH.TimeZone,
SGHH.DbDateTime
ORDER BY Media_Routing_Domain.EnterpriseName ,Skill_Group.EnterpriseName, SGHH.DateTime
04-04-2012 05:38 AM
Hi,
I see. I will try to correct it. Actually, I can see some interesting things. Will come back to you in a moment.
And, please, next time, don't use the formula 'it shows some error' - error messages are there to inform you what's wrong. If you were my customer, I would consider this support case 'somehow solved'.
G.
04-04-2012 06:15 AM
Hi,
remove these rows from the SELECT:
Interval = SGHH.DateTime,
Datepart(yy, SGHH.DateTime) as Year, Datepart(mm, SGHH.DateTime) as Month,
Datepart(ww, SGHH.DateTime) as Week,
Datepart(dy, SGHH.DateTime) as DOY,
Datepart(dw, SGHH.DateTime) as DOW,
RecoveryKey = SGHH.RecoveryKey,
TimeZone = SGHH.TimeZone,
DbDateTime = SGHH.DbDateTime
Your new GROUP BY clause:
GROUP BY
Skill_Group.EnterpriseName,
Skill_Group.SkillTargetID,
Media_Routing_Domain.EnterpriseName,
CONVERT(char(10),SGHH.DateTime,101)
Your new ORDER BY clause:
ORDER BY
Skill_Group.EnterpriseName,
Skill_Group.SkillTargetID,
Media_Routing_Domain.EnterpriseName,
CONVERT(char(10),SGHH.DateTime,101)
04-05-2012 12:06 AM
Hi Gergely,
I have made the changes, it is now giving any error, but when we run the report we are getting the half-hour interval report only for every skill group.
Seems to be change the query.
with Regards,
Manivannan
04-05-2012 01:03 AM
Hi,
then, to be quite honest with you, I don't know what you want. You said you want each row to represent one day.
G.
04-05-2012 01:12 AM
Gergely,
yeah i wanted to have have each row to represent one day consolidated, but still it is showing half hour interval. If you could provide the template it will be useful.
with Regards,
Manivannan
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide