- Cisco Community
- Technology and Support
- Collaboration
- Contact Center
- cuic report template - perskg22
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
cuic report template - perskg22
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Labels:
-
Other Contact Center
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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: