sql - Case statement and divide by zero error -
i'm getting divide 0 error , im not sure how handle it. using sql server database.
script:
select race , [2012] = max(case when a.[year] = 2012 [count] end) , [% inc dec] = ( max(cast(case when a.[year] null '' else a.[year] = 2011 [count] end float)) - max(cast(case when a.[year] null '' else a.[year] = 2012 [count] end float)) ) / max(cast(case when a.[year] null '' else a.[year] = 2012 [count] end float)) , [2011] = max(case when a.[year] = 2011 [count] end) , [% inc dec] = ( max(cast(case when a.[year] = 2010 [count] end float)) - max(cast(case when a.[year] = 2011 [count] end float)) ) / max(cast(case when a.[year] = 2010 [count] end float)) , [2010] = max(case when a.[year] = 2010 [count] end) , [2009] = max(case when a.[year] = 2009 [count] end) , [% inc dec] = ( max(cast(case when a.[year] = 2008 [count] end float)) - max(cast(case when a.[year] = 2009 [count] end float)) ) / max(cast(case when a.[year] = 2009 [count] end float)) @data i attempted change first 2 case statements incorrect. or push in right direction appreciated.
i'm assuming a alias @data, or it's in same table/source race , [count], data looks this:
race year count w 2012 10 w 2011 50 w 2010 100 b 2010 200 b 2009 75 and expect data decrease on time. i'd use self-join insure both high year , low year have values before subtract , divide, this:
select race, y1.[year] yr1, y1.[count] cnt1, y2.[year] yr2, y2.[count] cnt2, (cnt2 - cnt1) / cnt1 pct_inc_dec @data y1 inner join @data y2 on y1.race = y2.race , y1.year = y2.year - 1 -- y1 later year then don't have worry division 0 @ all, unless count zero.
with code provided, then, self-join applied on stored table after cross apply's pivot. can combine older year's criteria doesn't have go in own table:
declare @data table ( [year] int , [count] int , race varchar(50) ) insert @data select race ,[count], [year] (select dt.year year , sum(isnull(caucasian_enrollment, 0)) caucasian , sum(isnull(black_or_african_american_enrollment, 0)) black , sum(isnull(asian_enrollment,0)) asian , sum(isnull(native_hawaiian_pacific_islander_enrollment, 0)) native , sum(isnull(hispanic_enrollment, 0)) hispanic , sum(isnull(american_indian_or_alaskan_ative_enrollment, 0)) [american indian] , sum(isnull(multiracial_enrollment, 0)) multiracial , sum(isnull(unknown_ethnicity_enrollment, 0)) unknown , sum(isnull(male_enrollment, 0)) male, sum(isnull(female_enrollment, 0)) female, sum(isnull(total_enrollment, 0)) [total enrollment] mart.dbo.f_s fes inner join time dt on fes.time_key = dt.time_key year = '2012' group dt.year union select dt.year year , sum(isnull(caucasian_enrollment, 0)) caucasian , sum(isnull(black_or_african_american_enrollment, 0)) black , sum(isnull(asian_enrollment,0)) asian , sum(isnull(native_hawaiian_pacific_islander_enrollment, 0)) native, sum(isnull(hispanic_enrollment, 0)) hispanic, sum(isnull(american_indian_or_alaskan_ative_enrollment, 0)) [american indian] , sum(isnull(multiracial_enrollment, 0)) multiracial, sum(isnull(unknown_ethnicity_enrollment, 0)) unknown, sum(isnull(male_enrollment, 0)) male , sum(isnull(female_enrollment, 0)) female, sum(isnull(total_enrollment, 0)) [total enrollment] f5a_education_mart_iak12.dbo.fact_enrollment_school fes inner join dim_time dt on fes.time_key = dt.time_key year <= '2011' , year >= '2008' group dt.year ) d cross apply ( values ( 'caucasian', caucasian, [year]) , ( 'black', black, [year]) , ( 'asian', asian, [year]) , ('native', native, [year]) , ('hispanic', hispanic, [year]) , ('american indian', [american indian], [year]) , ('multiracial', multiracial, [year]) , ('unkown', unknown, [year]) , ('male', male, [year]) , ('female', female, [year]) , ('total enrollment', [total enrollment], [year]) ) ( race, [count], [year] ) group race select race, y1.[year] yr1, y1.[count] cnt1, y2.[year] yr2, y2.[count] cnt2, (cnt2 - cnt1) / cnt1 pct_inc_dec @data y1 inner join @data y2 on y1.race = y2.race , y1.year = y2.year - 1 -- y1 later year
Comments
Post a Comment