×

Warning message

  • Cisco Support Forums is in Read Only mode while the site is being migrated.
  • Cisco Support Forums is in Read Only mode while the site is being migrated.

cuic report template - perskg22

Unanswered Question
Apr 4th, 2012
User Badges:

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

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 0 (0 ratings)
Loading.
Gergely Szabo Wed, 04/04/2012 - 02:45
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

Hi,

is that a custom report?

If yes, why don't you try a GROUP BY clause in SQL?

G.

manivannan.para... Wed, 04/04/2012 - 03:27
User Badges:

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

Gergely Szabo Wed, 04/04/2012 - 04:00
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

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.

manivannan.para... Wed, 04/04/2012 - 05:21
User Badges:

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

Gergely Szabo Wed, 04/04/2012 - 05:38
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

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.

Gergely Szabo Wed, 04/04/2012 - 06:15
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

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)

manivannan.para... Thu, 04/05/2012 - 00:06
User Badges:

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

Gergely Szabo Thu, 04/05/2012 - 01:03
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

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.

manivannan.para... Thu, 04/05/2012 - 01:12
User Badges:

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

Actions

This Discussion