Calculations in SQL and preventing Divide By Zero

Question

I am trying to rework a query that is based on cursors.

The query calculates certain stats based on multiple values. In the snippet below the first, second and third CASE works out the happiness of Unit1. Any of these fields are 0 (they can never be NULL) I will get a Divide by Zero error. I could just add 1 to each field (Unit2 + 1) / (Unit1 + 1) and that will stop the error. However, it seems like a bodge and it will potentially give the incorrect result. ie. Unit1 needs the same amount of Unit2 to keep them happy. If I have one Unit1 and no Unit2 this bodge will give 100% happy for that check. So my first problem is how do I prevent the divide by zero but not distort the results. Each CASE gives me a % happy

Select 

CASE WHEN ((Unit2 / Unit1) * 100) > 100 Then 100 Else ((Unit2 / Unit1) * 100) END Unit1Happy1,
CASE WHEN ((Stock3 / (Unit1 * 2)) * 100) > 100 Then 100 Else ((Stock3 / (Unit1 * 2)) * 100) END Unit1Happy2,
CASE WHEN (((Drug3 + (Drug1 / 2)) / Unit1) * 100)  > 100 Then 100 Else (((Drug3 + (Drug1 / 2)) / Unit1) * 100)  END Unit1Happy3,
CASE WHEN (((Weapon6 + Weapon7 + Weapon8 + Weapon9) / Unit2) * 100) > 100 Then 100 ELSE (((Weapon6 + Weapon7 + Weapon8 + Weapon9) / Unit2) * 100) END Unit2Happ1,
CASE WHEN (((Stock2 + (Stock1 / 2)) / Unit2) * 100)  > 100 Then 100 Else (((Stock2 + (Stock1 / 2)) / Unit2) * 100) END Unit2Happ2

FROM tblUserFiles

My next problem is that I need to take to lowest value for each UnitHappiness and store that value in the table. So in tblUserFiles are 5 fields Unit1Happ, Unit2Happ .... Unit5Happ. Looking at the above query if Unit1Happy1 is the lowest figure I store that figure into Unit1Happ, If Unit2Happy is the lowest I store that etc.

My record Identifier is UserId and I need to run this for a given UserId or for the whole table.

What I am basically asking is:

• What is the best method to identify the lowest value of each Unit calculation?
• What is the best way to prevent the divide by zero error?
• Can I approach this problem in a better way?

Update

I am working through the suggestions posted in the answers below. As this is just a training exercise it may take a while. I do have a working query that gives the results I am looking for I am just not sure if the suggested answers would be more efficient.

Update tblUserFiles Set Unit1Happ = happyvals.Unit1Happiness, Unit2Happ = happyvals.Unit2Happiness, Unit3Happ = happyvals.Unit3Happiness, Unit4Happ = happyvals.Unit4Happiness, Unit5Happ = happyvals.Unit5Happiness FROM

(SELECT ch.UserId,
Case When ch.Unit1Happy1 < ch.Unit1Happy2 And ch.Unit1Happy1 < ch.Unit1Happy3 Then ch.Unit1Happy1
            When ch.Unit1Happy2 < ch.Unit1Happy1 And ch.Unit1Happy2 < ch.Unit1Happy3 Then ch.Unit1Happy2 
            Else ch.Unit1Happy3
End As Unit1Happiness,
CASE WHEN ch.Unit2Happy1 > ch.Unit2Happy2 THEN ch.Unit2Happy1
            ELSE ch.Unit2Happy2
END AS Unit2Happiness, 
ch.Unit3Happy1 AS Unit3Happiness,
ch.Unit4Happy1 AS Unit4Happiness,
ch.Unit5Happy1 AS Unit5Happiness
FROM
(

Select 
UserId,
CASE    WHEN Unit2 = 0 OR Unit1 = 0 THEN 0 
        WHEN ((Unit2 / Unit1) * 100) > 100 Then 100 
        ELSE ((Unit2 / Unit1) * 100) 
END Unit1Happy1,
CASE    WHEN Stock3 = 0 OR Unit1 = 0 THEN 0 
        WHEN ((Stock3 / (Unit1 * 2)) * 100) > 100 THEN 100 
        ELSE ((Stock3 / (Unit1 * 2)) * 100) 
END Unit1Happy2,
CASE    WHEN Unit1 = 0 THEN 0 
        WHEN Drug3 = 0 AND Drug1 = 0 THEN 0
        WHEN Drug1 = 0 THEN 
                        CASE WHEN (Drug3 / Unit1) * 100 > 100 THEN 100
                            ELSE (Drug3 / Unit1) * 100
                        END
        WHEN Drug3 = 0 THEN
                        CASE WHEN (Drug1 / 2) / Unit1 > 100 THEN 100
                            ELSE (Drug1 / 2) / Unit1
                        END
        ELSE 
            CASE WHEN (((Drug3 + (Drug1 / 2)) / Unit1) * 100) > 100 THEN 100
                ELSE (((Drug3 + (Drug1 / 2)) / Unit1) * 100)
            END

END Unit1Happy3,
CASE    WHEN Unit2 = 0 THEN 0 
        WHEN (Weapon6 + Weapon7 + Weapon8 + Weapon9) = 0 THEN 0
        WHEN (((Weapon6 + Weapon7 + Weapon8 + Weapon9) / Unit2) * 100) > 100 THEN 100
        ELSE (((Weapon6 + Weapon7 + Weapon8 + Weapon9) / Unit2) * 100)
END Unit2Happy1,
CASE    WHEN Unit2 = 0 THEN 0
        WHEN Stock1 = 0 AND Stock2 = 0 THEN 0
        WHEN Stock1 = 0 THEN
            CASE WHEN ((Stock2 / Unit2) * 100)  > 100 THEN 100
                ELSE ((Stock2 / Unit2) * 100)
            END
        WHEN Stock2 = 0 THEN
            CASE WHEN (((Stock1 / 2) / Unit2) * 100)  > 100 THEN 100
                ELSE (((Stock1 / 2) / Unit2) * 100)
            END
        WHEN (((Stock2 + (Stock1 / 2)) / Unit2) * 100)  > 100 THEN 100
        ELSE (((Stock2 + (Stock1 / 2)) / Unit2) * 100)
END Unit2Happy2,    
CASE    WHEN Unit2 = 0 OR Unit3 = 0 THEN 0 
        WHEN ((Unit2 / Unit3) * 100) > 100 THEN 100 
        ELSE ((Unit2 / Unit3) * 100) 
END Unit3Happy1,
CASE    WHEN Unit2 = 0 OR Unit4 = 0 THEN 0 
        WHEN ((Unit2 / Unit4) * 100) > 100 THEN 100 
        ELSE ((Unit2 / Unit4) * 100) 
END Unit4Happy1,
CASE    WHEN Unit2 = 0 OR Unit5 = 0 THEN 0 
        WHEN ((Unit2 / Unit5) * 100) > 100 THEN 100 
        ELSE ((Unit2 / Unit5) * 100) 
END Unit5Happy1
FROM tblUserFiles) ch) happyvals
Join tblUserFiles ON tblUserFiles.UserID = happyvals.UserID

Answer 1

put a nullif(Unit1, 0) around every divide by group ?

Answer 2

Something like this.

The idea is to wrap your division in a UDF (scalar user defined function)....and use the "Max(v)" trick.

The code below may not be perfect, I've supplying the idea.

Cursors are horrible performers, 99.9% of the time. Try to solve this without cursors.

/* or Create */
ALTER FUNCTION dbo.udfSafeDivision (@num float , @denom float)

RETURNS float

AS

BEGIN
    declare @returnValue float
    select @returnValue = 0

        if(isnull(@denom,0) != 0)
        BEGIN
            select @returnValue = convert(float, convert(float, @num)/convert(float, @denom))
        END
    return @returnValue


END


GO


IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
        drop table #TableOne
end


CREATE TABLE #TableOne
( 
SurrogateKey int IDENTITY(1001, 1), 
Unit1 int , 
Unit2 int , 
Stock1 int , 
Stock2 int , 
Stock3 int ,

Drug1 int , 
Drug3 int , 
Weapon6  int ,  Weapon7  int ,  Weapon8  int ,  Weapon9 int

)

Insert into #TableOne (Unit1, Unit2, Stock1, Stock2 , Stock3 , Drug1, Drug3 , Weapon6 , Weapon7 , Weapon8 , Weapon9)
select 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0
UNION ALL select 1 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0
UNION ALL select 1 , 2 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0
UNION ALL select 1 , 2 , 3 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0
UNION ALL select 1 , 2 , 3 , 4 , 0 , 0 , 0 , 0 , 0 , 0 , 0
UNION ALL select 1 , 2 , 3 , 4 , 5 , 0 , 0 , 0 , 0 , 0 , 0
UNION ALL select 1 , 2 , 3 , 4 , 5 , 6 , 0 , 0 , 0 , 0 , 0
UNION ALL select 1 , 2 , 3 , 4 , 5 , 6 , 7 , 0 , 0 , 0 , 0
UNION ALL select 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 0 , 0 , 0
UNION ALL select 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 0 , 0
UNION ALL select 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 0
UNION ALL select 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11


UNION ALL select 5 , 1 ,  0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0
UNION ALL select 5 , 0 ,  0 , 0 , 5 , 0 , 0 , 0 , 0 , 0 , 0
UNION ALL select 10 , 0 ,  0 , 0 , 0 , 5 , 10 , 0 , 0 , 0 , 0
UNION ALL select 0 , 1 ,  0 , 0 , 0 , 5 , 10 , 10 , 20 , 30 , 40
UNION ALL select 0 , 50 ,  20 , 10 , 0 , 0 , 0 , 0 , 0 , 0 , 0


SELECT 
  (SELECT Max(v) 
   FROM (VALUES (Unit1Happy1), (Unit1Happy2), (Unit1Happy3),  (Unit2Happ1), (Unit2Happ2)) AS value(v)) as [MaxValue]
   ,    '--------' as Sep1
   , derived1.*

FROM
(
Select 

CASE WHEN ((dbo.udfSafeDivision(Unit2 , Unit1)) * 100) > 100 Then 100 Else (dbo.udfSafeDivision(Unit2 , Unit1) * 100) END Unit1Happy1,
CASE WHEN (dbo.udfSafeDivision(Stock3 , (Unit1 * 2)) * 100) > 100 Then 100 Else (dbo.udfSafeDivision(Stock3 , (Unit1 * 2)) * 100) END Unit1Happy2,
CASE WHEN (dbo.udfSafeDivision((Drug3 + (Drug1 / 2)) , Unit1) * 100)  > 100 Then 100 Else (dbo.udfSafeDivision((Drug3 + (Drug1 / 2)) , Unit1) * 100)  END Unit1Happy3,
CASE WHEN (dbo.udfSafeDivision((Weapon6 + Weapon7 + Weapon8 + Weapon9) , Unit2) * 100) > 100 Then 100 ELSE (dbo.udfSafeDivision((Weapon6 + Weapon7 + Weapon8 + Weapon9) , Unit2) * 100) END Unit2Happ1,
CASE WHEN (dbo.udfSafeDivision((Stock2 + (Stock1 / 2)) , Unit2) * 100)  > 100 Then 100 Else (dbo.udfSafeDivision((Stock2 + (Stock1 / 2)) , Unit2) * 100) END Unit2Happ2
/* the below is to debug */
, '--' as Sep1
, Unit1, Unit2, Stock1, Stock2 , Stock3 , Drug1, Drug3 , Weapon6 , Weapon7 , Weapon8 , Weapon9
, dbo.udfSafeDivision(Unit2 , Unit1) as Div1
, dbo.udfSafeDivision(Stock3 , (Unit1 * 2)) as Div3

FROM #TableOne
) as derived1



IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
        drop table #TableOne
end