Search code examples
sqlsql-serverdivide

SQL Divide by Zero Error

I have an annoying problem which is stopping me from generating some data; The SQL job has 23 steps in total and fails on the 21st. 

-- Step 21 Create the table z1QReportOverview
-- Create z1QReportProjectOverview.sql
-- Project Overview - By Category (Part 4).sql
USE database
SELECT z1QReportProjectOverview1.[ERA Category] AS Category,
z1QReportProjectOverview1.[Total Projects Signed],
z1QReportProjectOverview1.[Total Spend Under Review],
z1QReportProjectOverview1.[Avg. Project Size],
z1QReportProjectOverview2.[Work in Progress],
z1QReportProjectOverview2.[Implemented],
z1QReportProjectOverview2.[No Savings],
z1QReportProjectOverview2.[Lost],
CONVERT(decimal(18,0),[Lost])/CONVERT(decimal(18,0),[Total Projects Signed]) AS [Loss Ratio],
z1QReportProjectOverview2.[Completed],
(
    CONVERT(decimal(18,0),([Completed]+[Implemented]))/
    CONVERT(decimal(18,0),([Completed]+[Implemented]+[Lost]))
) 
AS [Success Ratio],
z1QReportProjectOverview3.[Avg. Spend] AS [Average Spend],
z1QReportProjectOverview3.[Avg. Savings] AS [Average Savings],
z1QReportProjectOverview3.[Avg. Savings %] AS [Average Savings %]
INTO dbo.z1QReportProjectOverview
FROM dbo.z1QReportProjectOverview1
JOIN dbo.z1QReportProjectOverview2
ON (z1QReportProjectOverview1.[ERA Category] = z1QReportProjectOverview2.[ERA    Category])
JOIN dbo.z1QReportProjectOverview3
ON (z1QReportProjectOverview2.[ERA Category] = z1QReportProjectOverview3.[ERA     Category])
ORDER BY Category

I believe I know what is causing the divide by zero error.

The 'Lost' field is made up of three fields and in some cases (very rare) all 3 fields are 0 resulting in a 0 in the 'Lost' field.

I believe this is the main cause of the error but there is a second division there as well, I am pretty rubbish at SQL hence my question:

Where should I put the CASE WHEN clause?

-Most likely written this bit wrong as well :( "CASE When [Lost] = 0 SET [Total Projects Signed] = 0"

Any advice is much appreciated!

Solution

  • You can use CASE as to check if the dividing value is 0 or not.

    CASE WHEN CONVERT(decimal(18,0),[Lost]) <> 0 THEN 
        CONVERT(decimal(18,0),[Lost])/CONVERT(decimal(18,0),[Total Projects Signed]) 
     ELSE 0 END AS [Loss Ratio],
z1QReportProjectOverview2.[Completed],
CASE WHEN CONVERT(decimal(18,0),([Completed]+[Implemented]+[Lost])) <> 0 THEN 
      (CONVERT(decimal(18,0),([Completed]+[Implemented]))/CONVERT(decimal(18,0),([Completed]+[Implemented]+[Lost]))) 
    ELSE 0 END AS [Success Ratio],