cuic report template - perskg22

Unanswered Question
Apr 4th, 2012

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

I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Average Rating: 0 (0 ratings)
Gergely Szabo Wed, 04/04/2012 - 02:45

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

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

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

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

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

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

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

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

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

Login or Register to take actions

This Discussion

Posted April 4, 2012 at 2:37 AM
Stats:
Replies:9 Avg. Rating:
Views:894 Votes:0
Shares:0
Tags: No tags.

Discussions Leaderboard