October 16, 2009 at 1:58 am
Hi Please help
I get a devide by zero error when I run this query, can someone please show me how to resolve.
Declare
@EndPeriod Numeric(18,0),
@StartPeriod Numeric(18,0)
Set @EndPeriod = 200909
Set @StartPeriod = substring(convert(varchar(6), @EndPeriod),1 , 4) + '01'
BEGIN
Select x.race, x.period,
Calc = case when hires + terminations = 0 then 0
else Round(Convert(Float, hires + terminations) / Headcount*100,2) end,
Headcount, Terminations, Hires
from (
Select
Headcount = case when sum(case when Headcount ='Headcount' then 1 else 0 end)= 0 then 0
else sum(case when Headcount ='Headcount' then 1 else 0 end)end,
Hires = case when sum(case when Headcount = 'NewHire' then 1 else 0 end) = 0 then 0
else sum(case when Headcount = 'NewHire' then 1 else 0 end) end,
Terminations = case when - sum(case when Headcount = 'Termination'then 1 else 0 end) = 0 then 0
else - sum(case when Headcount = 'Termination'then 1 else 0 end) end,
dt.Race, dt.Period
FROM DTIHeadcount dt inner join ReportingStructure R
ON dt.CostCenter = R.CostCenter
Where dt.Period between @StartPeriod and @EndPeriod
and R.Consolidate in ('Retail Central Unit')
-- and R.Level3 in (@Level3)
and dt.staffno is not null
and dt.race not like 'sa%'
Group By dt.Race, dt.Period) x
Order By x.Period
END
October 16, 2009 at 2:02 am
What are your business rules for when Headcount =0 ?
October 16, 2009 at 2:15 am
Hi Thanks for the feedback
according to my thinking if headcount = 0 then that department should not even be there. anyway I found that headcount is not 0 but is gradually decreasing thus the problem arose where terminations + hires = 0
I resolved with
Calc = case when hires + terminations = 0 then 0 when headcount = 0 then 0
else Round(Convert(Float, hires + terminations) / Headcount*100,2) end
Thanks for the response
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply