Divide by Zero - Help!

[nedrob]

Anybody know of any tricks that would prevent a "divide by zero" error which
may occur in this query? This has been driving me nuts for days. I need to divide "GM YTD/Temp GM MTD" however some of these fields contain zero values and the obvious happens. BOL has not been a help and I was hoping someone out here would have a solution.
Thanks
Ron

SELECT Branch_ID,
'WTD HRS' = SUM(CASE WHEN DATEPART(WW,
CURRENT_TIMESTAMP) = DATEPART(WW, Pay_Ending_Date)
THEN [Bill Hours] ELSE 0 END),
[Temp GM MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN [Bill Amount] - [Pay Amount] ELSE 0 END),
[Temp GM YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN [Bill Amount] - [Pay Amount] ELSE 0 END),
'GM MTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN ([Bill Amount] - [Pay Amount])
+ Perm_GM ELSE 0 END),
[Perm Sales MTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) AND
MONTH(CURRENT_TIMESTAMP) = MONTH(Pay_Ending_Date)
THEN [Perm_GM] ELSE 0 END),
[Perm Sales YTD] = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date) THEN [Perm_GM] ELSE 0 END),
'GM YTD' = SUM(CASE WHEN YEAR(CURRENT_TIMESTAMP)
= YEAR(Pay_Ending_Date)
THEN ([Bill Amount] - [Pay Amount])
+ Perm_GM ELSE 0 END)
FROM GM_Master
GROUP BY Branch_ID

 

[sagn]

perhaps this is not what you are looking for but,
you use several case staements to decide the value
of fields, why not using a CASE statement to check for a zero valued denominator? Something like


select
Case
when denominator=0
then 'some acceptable value, ie zero'
else
numerator/denominator
end

from table



ds

 

[nedrob]

You know this is so crazy it might work. I'll try it
Thanks,
Ron

 

[redlam]

You could also try the isnull function.
From BOL:

ISNULL
Replaces NULL with the specified replacement value.

Syntax
ISNULL ( check_expression , replacement_value )