Divide by Zero Error Encountered

[renee35]

Could someone pleasssssse help me with the error above? Here is the code:

select
m.parentprofitcenterid,
workperiodnum,
fiscalyear,
c.customerdescription,
'TempsonStreet' = count (distinct o.candidateid),
'Clients' = count (distinct o.clientid),
'TotalHrs' = sum (isnull(o.totalhours,0)),
'RegHrs' = sum(isnull(o.reghours,0)),
'OtherHrs' = sum(isnull(o.otherhours,0)),
'AvgPayRate' = Case when sum(isnull(o.pay,0)) = 0 then 0 else sum(isnull(o.pay,0))/sum (isnull(o.totalhours,0)) end,
'AvgBillRate' = Case when sum(isnull(o.billing,0)) = 0 then 0 else sum(isnull(o.billing,0))/sum (isnull(o.totalhours,0)) end,
'FullTimeEqiv' = sum(o.totalhours)/35,
'AvgTotHrs' = Case when sum(isnull(o.totalhours,0)) = 0 then 0 else sum(o.totalhours)/count (distinct o.candidateid) end,
'AvgRegHrs' = Case when sum(isnull(o.reghours,0)) = 0 then 0 else sum(o.reghours)/count (distinct o.candidateid) end,
'AveOtherHrs' = Case when sum(isnull(o.otherhours,0)) = 0 then 0 else sum(o.otherhours)/count (distinct o.candidateid) end,
'MarkupPct' = case when sum(isnull(o.pay,0)) <> 0 then sum((isnull(o.billing,0) - isnull(o.pay,0)))/sum(isnull(o.pay,0))*100 else 0 end,
'TimeCardNormal' = SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)<7) then 1 else 0 end),
'TimeCardExceptions' = SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)>7) then 1 else 0 end),
'TotalTimeCards' = SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)<7) then 1 else 0 end)+
SUM(Case when o.timesheetid is not null and (datediff(d,o.weekending,o.createdate)>7) then 1 else 0 end)
from omsdetail o
INNER JOIN profitcentertoprofitcentermap m ON m.profitcenterid = o.profitcenterid
INNER JOIN customers c on c.custid = o.clientid
Group by m.parentprofitcenterid, workperiodnum, fiscalyear, customerdescription


If I remove these lines from the code:

--c.customerdescription,
--INNER JOIN customers c on c.custid = o.clientid
--and take customerdescription out of the group by, it works.


Thanks!

Thanks a bunch!!

-T

 

[gmmastros]

Read this:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-efficient-handling-of-divide

It explains several methods for handling the divide by 0 error.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[renee35]

Thanks!

Thanks a bunch!!

-T