=== Summary Report Name: === Agent Team Historical All Fields Daily == Microsoft SQL Error (when you run the Report SQL directly on the AW database) Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. === CUIC Error Details === com.cisco.ccbu.cuic.businesslogic.datasource.CuicDbException: DbException: CuicDataSourceServiceManagerImpl.getDataSet() { Nested SQLException; SQLState: S0001 Vendor code: 8134 Message: Divide by zero error encountered. } at com.cisco.ccbu.cuic.businesslogic.datasource.CuicDataSourceServiceManagerImpl.getDataSetBuilder(CuicDataSourceServiceManagerImpl.java:1013) at com.cisco.ccbu.cuic.businesslogic.engine.CuicDataProcessingCenterWorkerRunnable.runReport(CuicDataProcessingCenterWorkerRunnable.java:335) at com.cisco.ccbu.cuic.businesslogic.engine.CuicDataProcessingCenterWorkerRunnable.run(CuicDataProcessingCenterWorkerRunnable.java:142) at com.cisco.ccbu.infra.threads.InstrumentedRunnable.run(InstrumentedRunnable.java:88) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619) at com.cisco.ccbu.infra.threads.ThreadPoolThread.run(ThreadPoolThread.java:164) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Divide by zero error encountered. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197) at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4762) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1682) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:955) at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859) at com.cisco.ccbu.cuic.objectmodel.engine.CuicDataSetBuilderJexl.loadData(CuicDataSetBuilderJexl.java:100) at com.cisco.ccbu.cuic.businesslogic.datasource.CuicDataSourceServiceManagerImpl.getDataSetBuilder(CuicDataSourceServiceManagerImpl.java:1005) ... 7 more ====SQL from the Summary Report=== SELECT AgentTeamName= AgentSkillGroup.AgentTeamName , Supervisor= AgentSkillGroup.Supervisor, Media = AgentSkillGroup.Media, Interval = AgentSkillGroup.Interval, Year= AgentSkillGroup.Year, Month = AgentSkillGroup.Month, FullName = AgentSkillGroup.FullName, AgentSkillID= AgentSkillGroup.AgentSkillID, AgentTeamSkillID= AgentSkillGroup.AgentTeamSkillID, PriSupervisorSkillTargetID= AgentSkillGroup.PriSupervisorSkillTargetID, CallsAnswered= AgentSkillGroup.CallsAnswered, CallsHandled = AgentSkillGroup.CallsHandled, AbandRingCalls = AgentSkillGroup.AbandRingCalls, AbandRingCallsTime = AgentSkillGroup.AbandRingCallsTime, RedirectCalls = AgentSkillGroup.RedirectCalls, RedirectCallsTime= AgentSkillGroup.RedirectCallsTime, AbandonHoldCalls= AgentSkillGroup.AbandonHoldCalls, TransferInCalls = AgentSkillGroup.TransferInCalls, TransferOutCalls= AgentSkillGroup.TransferOutCalls, ConsultativeCalls= AgentSkillGroup.ConsultativeCalls, ConferenceInCalls= AgentSkillGroup.ConferenceInCalls, ConferenceOutCalls= AgentSkillGroup.ConferenceOutCalls, OutExtnCalls= AgentSkillGroup.OutExtnCalls, ShortCalls= AgentSkillGroup.ShortCalls, SupAssistCalls= AgentSkillGroup.SupAssistCalls, BargeInCalls= AgentSkillGroup.BargeInCalls, InterceptCalls= AgentSkillGroup.InterceptCalls, MonitorCalls= AgentSkillGroup.MonitorCalls, WhisperCalls= AgentSkillGroup.WhisperCalls, EmergencyAssistCalls= AgentSkillGroup.EmergencyAssistCalls, SupAssistCallsTime= AgentSkillGroup.SupAssistCallsTime, AgentOutCallsOnHold= AgentSkillGroup.AgentOutCallsOnHold, AgentOutCallsOnHoldTime= AgentSkillGroup.AgentOutCallsOnHoldTime, InCallsOnHold= AgentSkillGroup.InCallsOnHold, InCallsOnHoldTime= AgentSkillGroup.InCallsOnHoldTime, IntCallsOnHold= AgentSkillGroup.IntCallsOnHold, IntCallsOnHoldTime= AgentSkillGroup.IntCallsOnHoldTime, TalkTime= AgentSkillGroup.TalkTime, HandledCallsTime= AgentSkillGroup.HandledCallsTime, LoggedOnTime= AgentSkillGroup.LoggedOnTime, HoldTime= AgentSkillGroup.HoldTime, AvailTime= AgentHalfHour.AvailTime, NotReadyTime= AgentHalfHour.NotReadyTime, ReservedTime= AgentSkillGroup.ReservedTime, WrapTime= AgentSkillGroup.WrapTime, BusyOtherTime= AgentSkillGroup.BusyOtherTime, AnswerWaitTime= AgentSkillGroup.AnswerWaitTime, AutoOutCalls= AgentSkillGroup.AutoOutCalls, AutoOutCallsTime= AgentSkillGroup.AutoOutCallsTime, AutoOutCallsTalkTime= AgentSkillGroup.AutoOutCallsTalkTime, AutoOutCallsOnHold= AgentSkillGroup.AutoOutCallsOnHold, AutoOutCallsOnHoldTime= AgentSkillGroup.AutoOutCallsOnHoldTime, PreviewCalls= AgentSkillGroup.PreviewCalls, PreviewCallsTime= AgentSkillGroup.PreviewCallsTime, PreviewCallsTalkTime= AgentSkillGroup.PreviewCallsTalkTime , PreviewCallsOnHold= AgentSkillGroup.PreviewCallsOnHold, PreviewCallsOnHoldTime= AgentSkillGroup.PreviewCallsOnHoldTime, ReserveCalls= AgentSkillGroup.ReserveCalls, ReserveCallsTime= AgentSkillGroup.ReserveCallsTime, ReserveCallsTalkTime= AgentSkillGroup.ReserveCallsTalkTime, ReserveCallsOnHold= AgentSkillGroup.ReserveCallsOnHold, ReserveCallsOnHoldTime= AgentSkillGroup.ReserveCallsOnHoldTime, TalkAutoOutTime= AgentSkillGroup.TalkAutoOutTime, TalkPreviewTime= AgentSkillGroup.TalkAutoOutTime, TalkReserveTime= AgentSkillGroup.TalkReserveTime, AgentOutCallsTalkTime= AgentSkillGroup.AgentOutCallsTalkTime, AgentTerminatedCalls= AgentSkillGroup.AgentTerminatedCalls, CallbackMessages= AgentSkillGroup.CallbackMessages, CallbackMessagesTime= AgentSkillGroup.CallbackMessagesTime, ConsultativeCallsTime= AgentSkillGroup.ConsultativeCallsTime, ConferencedInCallsTime= AgentSkillGroup.ConferencedInCallsTime, ConferencedOutCallsTime= AgentSkillGroup.ConferencedOutCallsTime, HandledCallsTalkTime= AgentSkillGroup.HandledCallsTalkTime, InternalCallsRcvd= AgentSkillGroup.InternalCallsRcvd, InternalCallsRcvdTime= AgentSkillGroup.InternalCallsRcvdTime, InternalCalls= AgentSkillGroup.InternalCalls, InternalCallsTime= AgentSkillGroup.InternalCallsTime, TransferredInCallsTime= AgentSkillGroup.TransferredInCallsTime, TalkOtherTime= AgentSkillGroup.TalkOtherTime, TalkOutTime= AgentSkillGroup.TalkOutTime, InterruptedTime= AgentSkillGroup.InterruptedTime, WorkNotReadyTime= AgentSkillGroup.WorkNotReadyTime, WorkReadyTime= AgentSkillGroup.WorkReadyTime, NetConsultativeCalls= AgentSkillGroup.NetConsultativeCalls, NetConsultativeCallsTime= AgentSkillGroup.NetConsultativeCallsTime, NetConferencedOutCalls= AgentSkillGroup.NetConferencedOutCalls, NetConfOutCallsTime= AgentSkillGroup.NetConfOutCallsTime, NetTransferredOutCalls= AgentSkillGroup.NetTransferredOutCalls, AgentLoggedOnTime= AgentHalfHour.LoggedOnTime, AHT = ISNULL(AgentSkillGroup.HandledCallsTime / AgentSkillGroup.CallsHandled, 0), AHoldT = ISNULL(AgentSkillGroup.InCallsOnHoldTime / AgentSkillGroup.InCallsOnHold, 0), perActiveTime = ISNULL(AgentSkillGroup.TalkTime * 1.0 / AgentHalfHour.LoggedOnTime, 0), perHoldTime = ISNULL(AgentSkillGroup.HoldTime * 1.0 / AgentHalfHour.LoggedOnTime, 0), perNotActive = ISNULL(AgentHalfHour.AvailTime * 1.0 / AgentHalfHour.LoggedOnTime, 0), perNotReady = ISNULL(AgentHalfHour.NotReadyTime * 1.0 / AgentHalfHour.LoggedOnTime, 0), perReserved = ISNULL(AgentSkillGroup.ReservedTime * 1.0 / AgentHalfHour.LoggedOnTime, 0), perWrapup = ISNULL(AgentSkillGroup.WrapTime * 1.0 / AgentHalfHour.LoggedOnTime, 0), perBusyOther = ISNULL(AgentSkillGroup.BusyOtherTime * 1.0 / AgentHalfHour.LoggedOnTime, 0), Week= AgentSkillGroup.Week, Day= AgentSkillGroup.Day FROM (SELECT Agent_Team.EnterpriseName AS AgentTeamName, Supervisor=(SELECT Person.LastName + ', ' + Person.FirstName FROM Agent (nolock), Person (nolock) WHERE Agent.SkillTargetID = Agent_Team.PriSupervisorSkillTargetID AND Agent.PersonID = Person.PersonID), Media_Routing_Domain.EnterpriseName AS Media, dateadd(day, datediff(day,0, ASGHH.DateTime), 0) AS Interval, DATEPART(yy, ASGHH.DateTime) AS Year, DATEPART(mm, ASGHH.DateTime) AS Month, Person.LastName + ', ' + Person.FirstName AS FullName, Agent.SkillTargetID AS AgentSkillID, Agent_Team.AgentTeamID AS AgentTeamSkillID, Agent_Team.PriSupervisorSkillTargetID, SUM(ISNULL(ASGHH.CallsAnswered, 0)) AS CallsAnswered, SUM(ISNULL(ASGHH.CallsHandled, 0)) AS CallsHandled, SUM(ISNULL(ASGHH.AbandonRingCalls, 0)) AS AbandRingCalls, SUM(ISNULL(ASGHH.AbandonRingTime, 0)) AS AbandRingCallsTime, SUM(ISNULL(ASGHH.RedirectNoAnsCalls, 0)) AS RedirectCalls, SUM(ISNULL(ASGHH.RedirectNoAnsCallsTime, 0)) AS RedirectCallsTime, SUM(ISNULL(ASGHH.AbandonHoldCalls, 0)) AS AbandonHoldCalls, SUM(ISNULL(ASGHH.TransferredInCalls, 0)) AS TransferInCalls, SUM(ISNULL(ASGHH.TransferredOutCalls, 0)) AS TransferOutCalls, SUM(ISNULL(ASGHH.ConsultativeCalls, 0)) AS ConsultativeCalls, SUM(ISNULL(ASGHH.ConferencedInCalls, 0)) AS ConferenceInCalls, SUM(ISNULL(ASGHH.ConferencedOutCalls, 0)) AS ConferenceOutCalls, SUM(ISNULL(ASGHH.AgentOutCalls, 0)) AS OutExtnCalls, SUM(ISNULL(ASGHH.AgentOutCalls, 0)) AS AgentOutCalls, SUM(ISNULL(ASGHH.ShortCalls, 0)) AS ShortCalls, SUM(ISNULL(ASGHH.SupervAssistCalls, 0)) AS SupAssistCalls, SUM(ISNULL(ASGHH.BargeInCalls, 0)) AS BargeInCalls, SUM(ISNULL(ASGHH.InterceptCalls, 0)) AS InterceptCalls, SUM(ISNULL(ASGHH.MonitorCalls, 0)) AS MonitorCalls, SUM(ISNULL(ASGHH.WhisperCalls, 0)) AS WhisperCalls, SUM(ISNULL(ASGHH.EmergencyAssists, 0)) AS EmergencyAssistCalls, SUM(ISNULL(ASGHH.SupervAssistCallsTime, 0)) AS SupAssistCallsTime, SUM(ISNULL(ASGHH.AgentOutCallsOnHold, 0)) AS AgentOutCallsOnHold, SUM(ISNULL(ASGHH.AgentOutCallsOnHoldTime, 0)) AS AgentOutCallsOnHoldTime, SUM(ISNULL(ASGHH.IncomingCallsOnHold, 0)) AS InCallsOnHold, SUM(ISNULL(ASGHH.IncomingCallsOnHoldTime, 0)) AS InCallsOnHoldTime, SUM(ISNULL(ASGHH.InternalCallsOnHold, 0)) AS IntCallsOnHold, SUM(ISNULL(ASGHH.InternalCallsOnHoldTime, 0)) AS IntCallsOnHoldTime, sum(isnull(ASGHH.TalkInTime,0)) + sum(isnull(ASGHH.TalkOutTime,0)) + sum(isnull(ASGHH.TalkOtherTime,0)) + sum(isnull(ASGHH.TalkAutoOutTime,0)) + sum(isnull(ASGHH.TalkPreviewTime,0)) + sum(isnull(ASGHH.TalkReserveTime,0))AS TalkTime, SUM(ISNULL(ASGHH.HandledCallsTime, 0)) AS HandledCallsTime, SUM(ISNULL(ASGHH.LoggedOnTime, 0)) AS LoggedOnTime, SUM(ISNULL(ASGHH.HoldTime, 0)) AS HoldTime, SUM(ISNULL(ASGHH.AvailTime, 0)) AS AvailTime, SUM(ISNULL(ASGHH.NotReadyTime, 0)) AS NotReadyTime, SUM(ISNULL(ASGHH.ReservedStateTime, 0)) AS ReservedTime, SUM(ISNULL(ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime, 0)) AS WrapTime, SUM(ISNULL(ASGHH.BusyOtherTime, 0)) AS BusyOtherTime, SUM(ISNULL(ASGHH.AnswerWaitTime, 0)) AS AnswerWaitTime, SUM(ISNULL(ASGHH.AutoOutCalls, 0)) AS AutoOutCalls, SUM(ISNULL(ASGHH.AutoOutCallsTime, 0)) AS AutoOutCallsTime, SUM(ISNULL(ASGHH.AutoOutCallsTalkTime, 0)) AS AutoOutCallsTalkTime, SUM(ISNULL(ASGHH.AutoOutCallsOnHold, 0)) AS AutoOutCallsOnHold, SUM(ISNULL(ASGHH.AutoOutCallsOnHoldTime, 0)) AS AutoOutCallsOnHoldTime, SUM(ISNULL(ASGHH.PreviewCalls, 0)) AS PreviewCalls, SUM(ISNULL(ASGHH.PreviewCallsTime, 0)) AS PreviewCallsTime, SUM(ISNULL(ASGHH.PreviewCallsTalkTime, 0)) AS PreviewCallsTalkTime, SUM(ISNULL(ASGHH.PreviewCallsOnHold, 0)) AS PreviewCallsOnHold, SUM(ISNULL(ASGHH.PreviewCallsOnHoldTime, 0)) AS PreviewCallsOnHoldTime, SUM(ISNULL(ASGHH.ReserveCalls, 0)) AS ReserveCalls, SUM(ISNULL(ASGHH.ReserveCallsTime, 0)) AS ReserveCallsTime, SUM(ISNULL(ASGHH.ReserveCallsTalkTime, 0)) AS ReserveCallsTalkTime, SUM(ISNULL(ASGHH.ReserveCallsOnHold, 0)) AS ReserveCallsOnHold, SUM(ISNULL(ASGHH.ReserveCallsOnHoldTime, 0)) AS ReserveCallsOnHoldTime, SUM(ISNULL(ASGHH.TalkAutoOutTime, 0)) AS TalkAutoOutTime, SUM(ISNULL(ASGHH.TalkPreviewTime, 0)) AS TalkPreviewTime, SUM(ISNULL(ASGHH.TalkReserveTime, 0)) AS TalkReserveTime, SUM(ISNULL(ASGHH.AgentOutCallsTalkTime, 0)) AS AgentOutCallsTalkTime, SUM(ISNULL(ASGHH.AgentTerminatedCalls, 0)) AS AgentTerminatedCalls, SUM(ISNULL(ASGHH.CallbackMessages, 0)) AS CallbackMessages, SUM(ISNULL(ASGHH.CallbackMessagesTime, 0)) AS CallbackMessagesTime, SUM(ISNULL(ASGHH.ConsultativeCallsTime, 0)) AS ConsultativeCallsTime, SUM(ISNULL(ASGHH.ConferencedInCallsTime, 0)) AS ConferencedInCallsTime, SUM(ISNULL(ASGHH.ConferencedOutCallsTime, 0)) AS ConferencedOutCallsTime, SUM(ISNULL(ASGHH.HandledCallsTalkTime, 0)) AS HandledCallsTalkTime, SUM(ISNULL(ASGHH.InternalCallsRcvd, 0)) AS InternalCallsRcvd, SUM(ISNULL(ASGHH.InternalCallsRcvdTime, 0)) AS InternalCallsRcvdTime, SUM(ISNULL(ASGHH.InternalCalls, 0)) AS InternalCalls, SUM(ISNULL(ASGHH.InternalCallsTime, 0)) AS InternalCallsTime, SUM(ISNULL(ASGHH.TransferredInCallsTime, 0)) AS TransferredInCallsTime, SUM(ISNULL(ASGHH.TalkOtherTime, 0)) AS TalkOtherTime, SUM(ISNULL(ASGHH.TalkOutTime, 0)) AS TalkOutTime, SUM(ISNULL(ASGHH.InterruptedTime, 0)) AS InterruptedTime, SUM(ISNULL(ASGHH.WorkNotReadyTime, 0)) AS WorkNotReadyTime, SUM(ISNULL(ASGHH.WorkReadyTime, 0)) AS WorkReadyTime, SUM(ISNULL(ASGHH.NetConsultativeCalls, 0)) AS NetConsultativeCalls, SUM(ISNULL(ASGHH.NetConsultativeCallsTime, 0)) AS NetConsultativeCallsTime, SUM(ISNULL(ASGHH.NetConferencedOutCalls, 0)) AS NetConferencedOutCalls, SUM(ISNULL(ASGHH.NetConfOutCallsTime, 0)) AS NetConfOutCallsTime, SUM(ISNULL(ASGHH.NetTransferredOutCalls, 0)) AS NetTransferredOutCalls, DATEPART(Week, ASGHH.DateTime) AS Week, DATEPART(dd, ASGHH.DateTime) AS Day FROM Agent (nolock), --Agent_Skill_Group_Half_Hour ASGHH (nolock), Agent_Skill_Group_Interval ASGHH (nolock), Agent_Team_Member (nolock), Agent_Team (nolock), Skill_Group (nolock), Person (nolock), Media_Routing_Domain (nolock) WHERE Agent_Team_Member.AgentTeamID in (5017) AND ASGHH.DateTime >= '2013-02-01 07:45:00' AND ASGHH.DateTime <= '2013-02-19 18:15:00' AND ( ASGHH.SkillGroupSkillTargetID = Skill_Group.SkillTargetID) and ( Media_Routing_Domain.MRDomainID = Skill_Group.MRDomainID) and ( Skill_Group.SkillTargetID NOT IN (SELECT BaseSkillTargetID FROM Skill_Group (nolock) WHERE (Priority > 0) AND (Deleted <> 'Y'))) and ( Agent.SkillTargetID = ASGHH.SkillTargetID ) and ( Agent.SkillTargetID = Agent_Team_Member.SkillTargetID ) and ( Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID ) and ( Agent.PersonID = Person.PersonID ) GROUP BY Agent_Team.AgentTeamID, Agent_Team.EnterpriseName, Agent_Team.PriSupervisorSkillTargetID, Agent.SkillTargetID, Person.LastName, Person.FirstName, Media_Routing_Domain.EnterpriseName, DATEPART(yy, ASGHH.DateTime) , DATEPART(mm, ASGHH.DateTime) , DATEPART(ww, ASGHH.DateTime), DATEPART(dd, ASGHH.DateTime), dateadd(day, datediff(day,0, ASGHH.DateTime), 0) ) AgentSkillGroup, (SELECT Media = Media_Routing_Domain.EnterpriseName, LoggedOnTime =sum( AHH.LoggedOnTime), AvailTime = SUM(ISNULL(AHH.AvailTime, 0)), NotReadyTime = SUM(ISNULL(AHH.NotReadyTime, 0)), AgentSkillID = AHH.SkillTargetID, dateadd(day, datediff(day,0, AHH.DateTime), 0) AS Interval FROM Agent (nolock), Agent_Interval AHH (nolock), Media_Routing_Domain (nolock) WHERE Agent.SkillTargetID= AHH.SkillTargetID AND AHH.MRDomainID = Media_Routing_Domain.MRDomainID AND (AHH.DateTime >= '2013-02-01 07:45:00') AND (AHH.DateTime <= '2013-02-19 18:15:00') GROUP BY AHH.SkillTargetID,Media_Routing_Domain.EnterpriseName, dateadd(day, datediff(day,0, AHH.DateTime), 0) ) AgentHalfHour WHERE AgentSkillGroup.AgentSkillID = AgentHalfHour.AgentSkillID and AgentSkillGroup.Interval = AgentHalfHour.Interval and AgentSkillGroup.Media = AgentHalfHour.Media ORDER BY AgentSkillGroup.AgentTeamName,AgentSkillGroup.FullName,AgentSkillGroup.Media,AgentSkillGroup.AgentSkillID,AgentSkillGroup.Interval