CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
msvuze
msvuze

Reputation: 1397

MS SQL 2012 - Divide by zero error encountered

I'm using MS SQL 20012 and have a Stored Procedure the code is like this:

USE [StatsAa]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DriverVSComp] 
    -- Add the parameters for the stored procedure here
    @PERSON_NO nvarchar(50)
AS
    SET NOCOUNT ON;

DECLARE @weeks int = 26

;WITH Person AS (
   SELECT
     datediff(ww, DATE_GIVEN, getdate())+1 AS Week,
     AVG(CHECK_AMOUNT) AS Person_Check_Amount
   FROM CHECKS
   WHERE PERSON_NO=11  AND DATE_GIVEN >= dateadd(ww, -@weeks, getdate())
   GROUP BY datediff(ww, DATE_GIVEN, getdate()) +1
)

, Company AS (
    SELECT week,
  AVG (COMPANY_Check_Amount) AS COMPANY_Check_Amount
    FROM (
       SELECT
         datediff(ww, DATE_GIVEN, getdate())+1 AS Week,
         SUM(CHECK_AMOUNT)/COUNT(DISTINCT PERSON_NO) AS COMPANY_Check_Amount
       FROM CHECKS
       WHERE PERSON_NO<>11  AND DATE_GIVEN >= dateadd(ww, -@weeks, getdate())
       GROUP BY datediff(ww, DATE_GIVEN, getdate())+1 
    ) t
  GROUP BY Week
)

SELECT c.week
 , isnull(Person_Check_Amount,0)  Person_Check_Amount
 , isnull(Company_Check_Amount,0) Company_Check_Amount
FROM Person p
FULL OUTER JOIN Company c ON c.week = p.week
ORDER BY Week DESC

but I keep getting this error: 

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 

[Microsoft][SQL Server Native Client 11.0][SQL Server]Divide by zero error encountered.

When I run is from MSSMS it works fine and displays results but only after I ran the following once:

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

but the error still exists when I run the page (.asp page)

I was reading that this is because some values are Null but how do I put this into a stored procedure safely ? It's really important that no data is missed/passed/ignored because it has a Null value, because then it would offset the Drivers and the Comp. DATA and AVERAGES. Is it possible to set a zero '0' or the default value when it runs into a NULL ? And also, how do I find what column/row is giving it a problem.

As Always, Thanks so much for the help in advance!

Upvotes: 1

Views: 5465

Answers (1)

Vlad G.
Vlad G.

Reputation: 2147

You would get a Divide by zero error in this expression in one of 2 cases: 1) there are no records for a given Week and b) all PERSON_NO for the Week are NULL (assuming that column is nullable)

SUM(CHECK_AMOUNT)/COUNT(DISTINCT PERSON_NO) AS COMPANY_Check_Amount

Try replacing it with this:

CASE WHEN COUNT(DISTINCT PERSON_NO) = 0 THEN 0
ELSE SUM(CHECK_AMOUNT)/COUNT(DISTINCT PERSON_NO) 
END AS COMPANY_Check_Amount

Upvotes: 1

Related Questions