Avoid division by 0 errors

[rivard]

Hi when i run the following view i get division by 0 errors for the new data this month. can someone show me how to alter it so that only certain parts of the query result in a 0 number and not the entire statement. thanks.

CREATE VIEW dbo.viewTopOfHierarchy
AS
SELECT TOP 100 PERCENT Yr, Period, COUNT(Employee) AS [Number Of Technicians], ID AS [Month], SUM([Reg Hrs]) AS [Reg Hrs], SUM([OT Hrs]) AS [OT Hrs],
SUM([Stand Down]) AS [Stand Down], SUM([On Call]) AS [On Call], SUM([Personal Milage]) AS [Personal Mileage], SUM([Total Expenses])
AS [Total Expenses], SUM([Total Hours Distributed]) AS [Total Hours Distributed], SUM([Total Volume SA]) AS [Total Volume SA], SUM([Total Volume SF])
AS [Total Volume SF], SUM([Total Duration SA]) AS [Total Duration SA], SUM([Total Duration SF]) AS [Total Duration SF],
FLOOR(100 * ((SUM([Total Duration SF]) + SUM([Total Duration SA])) / (SUM([Total Volume SA]) + SUM([Total Volume SF])))) / 100 AS [Avg Duration],
SUM([Total Hours Invoiced]) AS [Total Hours Invoiced], SUM([Total Hours Undistributed]) AS [Total Hours Undistributed], FLOOR(100 * (SUM([OT Hrs])
* 100 / SUM([Reg Hrs]))) / 100 AS [%OT], FLOOR(100 * (SUM([Total Hours Distributed]) / SUM([Total Hours Invoiced]) * 100))
/ 100 AS [ % Hours Distrubuted in Awas], FLOOR(100 * (SUM([Total Hours Distributed]) / COUNT(Employee)))
/ 100 AS [Average Work Hours Per Technician], SUM([Reg Hrs]) + SUM([OT Hrs]) + SUM([Stand Down]) + SUM([Stand Down]) + SUM([On Call])
+ SUM([Personal Milage]) + SUM([Total Expenses]) + SUM([Total Hours Distributed]) + SUM([Total Volume SA]) + SUM([Total Volume SF])
+ SUM(AvgDuration) + SUM([Total Hours Invoiced]) + SUM([Total Hours Undistributed]) + SUM([%OT]) AS YTD
FROM dbo.viewScorecard
GROUP BY Yr, Period, ID
ORDER BY ID



sorry its very complicaed and didnt post well.

[ronverdonk]

I guess you have to enclose your divisors in IFNULL() statements. See http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html

Ronald :cool: