Comparison script that looks at the change between selected variables ACS 2014 Dataset and the Census 2000 Dataset

EJ 2014 to 2000 Compare.sql

SELECT        
b.OBJECTID, 
a.GEOID, 
a.county, 
a.TotalPop_ACS2014, 
Case When a.TotalPop_ACS2014 - a.PopChange<0 Then 0 Else a.TotalPop_ACS2014 - a.PopChange END  AS Pop_2000, 
CASE When (a.TotalPop_ACS2014 - a.PopChange)<0 Then a.TotalPop_ACS2014 
Else a.PopChange END as PopChange, 
CASE 
WHEN a.PopChange < 0 AND a.TotalPop_ACS2014 = 0 THEN -100
WHEN a.TotalPop_ACS2014 > 0 AND (a.TotalPop_ACS2014 - a.PopChange)>0 THEN ((a.PopChange) / (a.TotalPop_ACS2014 - a.PopChange ))*100 
WHEN a.PopChange = 0 THEN 0 
ELSE 100 END AS TotalPopPctChange,  

a.TotalWhitePop_ACS2014 as White_Alone_2014, 
Case When a.TotalWhitePop_ACS2014 - a.WhitePopChange<0 Then 0 Else a.TotalWhitePop_ACS2014 - a.WhitePopChange END  AS White_Alone_2000,
CASE When (a.TotalWhitePop_ACS2014 - a.WhitePopChange)<0 Then a.TotalWhitePop_ACS2014 
Else a.WhitePopChange END as WhitePopChange, 
CASE 
WHEN a.WhitePopChange < 0 AND a.TotalWhitePop_ACS2014 = 0 THEN -100
WHEN a.TotalWhitePop_ACS2014 > 0 AND (a.TotalWhitePop_ACS2014 - a.WhitePopChange)>0 THEN ((a.WhitePopChange) / (a.TotalWhitePop_ACS2014 - a.WhitePopChange ))*100 
WHEN a.WhitePopChange = 0 THEN 0 
ELSE 100 END AS WhiteAlonePopPctChange,  

a.TotalBlackPop_ACS2014 as Black_Alone_2014, 
Case When a.TotalBlackPop_ACS2014 - a.BlackPopChange<0 Then 0 Else a.TotalBlackPop_ACS2014 - a.BlackPopChange END  AS Black_Alone_2000,
CASE When (a.TotalBlackPop_ACS2014 - a.BlackPopChange)<0 Then a.TotalBlackPop_ACS2014 
Else a.BlackPopChange END as BlackPopChange, 
CASE 
WHEN a.BlackPopChange < 0 AND a.TotalBlackPop_ACS2014 = 0 THEN -100
WHEN a.TotalBlackPop_ACS2014 > 0 AND (a.TotalBlackPop_ACS2014 - a.BlackPopChange)>0 THEN ((a.BlackPopChange) / (a.TotalBlackPop_ACS2014 - a.BlackPopChange ))*100 
WHEN a.BlackPopChange = 0 THEN 0 
ELSE 100 END AS BlackAlonePopPctChange,  

a.Total_Hispanic_Latino_Pop_ACS2014 as Hispanic_Alone_2014, 
Case When a.Total_Hispanic_Latino_Pop_ACS2014 - a.Hispanic_LatinoPopChange<0 Then 0 Else a.Total_Hispanic_Latino_Pop_ACS2014 - a.Hispanic_LatinoPopChange END  AS Hispanic_Alone_2000,
CASE When (a.Total_Hispanic_Latino_Pop_ACS2014 - a.Hispanic_LatinoPopChange)<0 Then a.Total_Hispanic_Latino_Pop_ACS2014 
Else a.Hispanic_LatinoPopChange END as Hispanic_LatinoPopChange, 
CASE 
WHEN a.Hispanic_LatinoPopChange < 0 AND a.Total_Hispanic_Latino_Pop_ACS2014 = 0 THEN -100
WHEN a.Total_Hispanic_Latino_Pop_ACS2014 > 0 AND (a.Total_Hispanic_Latino_Pop_ACS2014 - a.Hispanic_LatinoPopChange)>0 THEN ((a.Hispanic_LatinoPopChange) / (a.Total_Hispanic_Latino_Pop_ACS2014 - a.Hispanic_LatinoPopChange ))*100 
WHEN a.Hispanic_LatinoPopChange = 0 THEN 0 
ELSE 100 END AS HispanicLatinoPopPctChange,

a.TotalAsian_Pacific_IslanderPop_ACS2014 as Asian_Pacific_Islander_2014, 
Case When a.TotalAsian_Pacific_IslanderPop_ACS2014 - a.Asian_Pacific_IslanderPopChange<0 Then 0 Else a.TotalAsian_Pacific_IslanderPop_ACS2014 - a.Asian_Pacific_IslanderPopChange END  AS Asian_Pacific_Islander_2000,
CASE When (a.TotalAsian_Pacific_IslanderPop_ACS2014 - a.Asian_Pacific_IslanderPopChange)<0 Then a.TotalAsian_Pacific_IslanderPop_ACS2014 
Else a.Asian_Pacific_IslanderPopChange END as Asian_Pacific_IslanderPopChange, 
CASE 
WHEN a.Asian_Pacific_IslanderPopChange < 0 AND a.TotalAsian_Pacific_IslanderPop_ACS2014 = 0 THEN -100
WHEN a.TotalAsian_Pacific_IslanderPop_ACS2014 > 0 AND (a.TotalAsian_Pacific_IslanderPop_ACS2014 - a.Asian_Pacific_IslanderPopChange)>0 THEN ((a.Asian_Pacific_IslanderPopChange) / (a.TotalAsian_Pacific_IslanderPop_ACS2014 - a.Asian_Pacific_IslanderPopChange ))*100 
WHEN a.Asian_Pacific_IslanderPopChange = 0 THEN 0 
ELSE 100 END AS AsianPacificPopPctChange,

a.POP_ZVHHS_ACS2014 as POP_ZVHHS_ACS2014, 
Case When a.POP_ZVHHS_ACS2014 - a.POP_ZVHHS_Change<0 Then 0 Else a.POP_ZVHHS_ACS2014 - a.POP_ZVHHS_Change END  AS POP_ZVHHS_2000,
CASE When (a.POP_ZVHHS_ACS2014 - a.POP_ZVHHS_Change)<0 Then a.POP_ZVHHS_ACS2014 
Else a.POP_ZVHHS_Change END as POP_ZVHHS_Change, 
CASE 
WHEN a.POP_ZVHHS_Change < 0 AND a.POP_ZVHHS_ACS2014 = 0 THEN -100
WHEN a.POP_ZVHHS_ACS2014 > 0 AND (a.POP_ZVHHS_ACS2014 - a.POP_ZVHHS_Change)>0 THEN ((a.POP_ZVHHS_Change) / (a.POP_ZVHHS_ACS2014 - a.POP_ZVHHS_Change))*100 
WHEN a.POP_ZVHHS_Change = 0 THEN 0 
ELSE 100 END AS POP_ZVHHS_PctChange,

a.POP_LEP_ACS2014 as POP_LEP_ACS2014, 
Case When a.POP_LEP_ACS2014 - a.POP_LEP_Change<0 Then 0 Else a.POP_LEP_ACS2014 - a.POP_LEP_Change END  AS POP_LEP_2000,
CASE When (a.POP_LEP_ACS2014 - a.POP_LEP_Change)<0 Then a.POP_LEP_ACS2014 
Else a.POP_LEP_Change END as POP_LEP_Change, 
CASE 
WHEN a.POP_LEP_Change < 0 AND a.POP_LEP_ACS2014 = 0 THEN -100
WHEN a.POP_LEP_ACS2014 > 0 AND (a.POP_LEP_ACS2014 - a.POP_LEP_Change)>0 THEN ((a.POP_LEP_Change) / (a.POP_LEP_ACS2014 - a.POP_LEP_Change))*100 
WHEN a.POP_LEP_Change = 0 THEN 0 
ELSE 100 END AS POP_LEP_PctChange,

a.SPFAM_ACS2014 as SPFAM_ACS2014, 
Case When a.SPFAM_ACS2014 - a.SPFAM_Change<0 Then 0 Else a.SPFAM_ACS2014 - a.SPFAM_Change END  AS SPFAM_2000,
CASE When (a.SPFAM_ACS2014 - a.SPFAM_Change)<0 Then a.SPFAM_ACS2014 
Else a.SPFAM_Change END as SPFAM_Change, 
CASE 
WHEN a.SPFAM_Change < 0 AND a.SPFAM_ACS2014 = 0 THEN -100
WHEN a.SPFAM_ACS2014 > 0 AND (a.SPFAM_ACS2014 - a.SPFAM_Change)>0 THEN ((a.SPFAM_Change) / (a.SPFAM_ACS2014 - a.SPFAM_Change))*100 
WHEN a.SPFAM_Change = 0 THEN 0 
ELSE 100 END AS SPFAM_PctChange,

a.POP_HUS_RENT50_ACS2014 as POP_HUS_RENT50_ACS2014, 
Case When a.POP_HUS_RENT50_ACS2014 - a.POP_HUS_RENT50_Change<0 Then 0 Else a.POP_HUS_RENT50_ACS2014 - a.POP_HUS_RENT50_Change END  AS POP_HUS_RENT50_2000,
CASE When (a.POP_HUS_RENT50_ACS2014 - a.POP_HUS_RENT50_Change)<0 Then a.POP_HUS_RENT50_ACS2014 
Else a.POP_HUS_RENT50_Change END as POP_HUS_RENT50_Change, 
CASE 
WHEN a.POP_HUS_RENT50_Change < 0 AND a.POP_HUS_RENT50_ACS2014 = 0 THEN -100
WHEN a.POP_HUS_RENT50_ACS2014 > 0 AND (a.POP_HUS_RENT50_ACS2014 - a.POP_HUS_RENT50_Change)>0 THEN ((a.POP_HUS_RENT50_Change) / (a.POP_HUS_RENT50_ACS2014 - a.POP_HUS_RENT50_Change))*100 
WHEN a.POP_HUS_RENT50_Change = 0 THEN 0 
ELSE 100 END AS RENT50_PctChange,

a.Pop65plus_ACS2014, 
Case When a.Pop65plus_ACS2014 - a.Pop65PlusChange<0 Then 0 Else a.Pop65plus_ACS2014 - a.Pop65PlusChange END  AS Pop65plus_2000, 
CASE When (a.Pop65plus_ACS2014 - a.Pop65PlusChange)<0 Then a.Pop65plus_ACS2014 Else a.Pop65PlusChange END as Pop65PlusChange,
CASE 
WHEN a.Pop65PlusChange < 0 AND a.Pop65plus_ACS2014 = 0 THEN -100
WHEN a.Pop65plus_ACS2014 > 0 AND (a.Pop65plus_ACS2014 - a.Pop65PlusChange)>0 THEN ((a.Pop65PlusChange) / (a.Pop65plus_ACS2014 - a.Pop65PlusChange))*100 
WHEN a.Pop65PlusChange = 0 THEN 0 
ELSE 100 END AS Pop65PlusPctChange,
a.Age65plusSOT, 
 
a.Veterans_ACS2014, 
Case When a.Veterans_ACS2014 - a.VeteransChange<0 Then 0 Else a.Veterans_ACS2014 - a.VeteransChange END  AS Veterans_2000, 
CASE When (a.Veterans_ACS2014 - a.VeteransChange)<0 Then a.Veterans_ACS2014 Else a.VeteransChange END as VeteransChange,
CASE 
WHEN a.VeteransChange < 0 AND a.Veterans_ACS2014 = 0 THEN -100
WHEN a.Veterans_ACS2014 > 0 AND (a.Veterans_ACS2014 - a.VeteransChange)>0 THEN ((a.VeteransChange) / (a.Veterans_ACS2014 - a.VeteransChange))*100 
WHEN a.VeteransChange = 0 THEN 0 
ELSE 100 END AS VeteransPopPctChange,
a.VeteransSOT,

a.LowIncomePop_ACS2014, 
Case When a.LowIncomePop_ACS2014 - a.LowIncomePopChange<0 Then 0 Else a.LowIncomePop_ACS2014 - a.LowIncomePopChange END  AS LowIncomePop_2000, 
CASE When (a.LowIncomePop_ACS2014 - a.LowIncomePopChange)<0 Then a.LowIncomePop_ACS2014 Else a.LowIncomePopChange END as LowIncomePopChange,
CASE 
WHEN a.LowIncomePopChange < 0 AND a.LowIncomePop_ACS2014 = 0 THEN -100
WHEN a.LowIncomePop_ACS2014 > 0 AND (a.LowIncomePop_ACS2014 - a.LowIncomePopChange)>0 THEN ((a.LowIncomePopChange) / (a.LowIncomePop_ACS2014 - a.LowIncomePopChange))*100 
WHEN a.LowIncomePopChange = 0 THEN 0 
ELSE 100 END AS LowIncomePopPctChange,
a.LowIncPopSOT,

a.DisabledPop_ACS2014,
Case When a.DisabledPop_ACS2014 - a.DisabledPopChange<0 Then 0 Else a.DisabledPop_ACS2014 - a.DisabledPopChange END  AS DisabledPop_2000, 
CASE When (a.DisabledPop_ACS2014 - a.DisabledPopChange)<0 Then a.DisabledPop_ACS2014 Else a.DisabledPopChange END as DisabledPopChange,
CASE 
WHEN a.DisabledPopChange < 0 AND a.DisabledPop_ACS2014 = 0 THEN -100
WHEN a.DisabledPop_ACS2014 > 0 AND (a.DisabledPop_ACS2014 - a.DisabledPopChange)>0 THEN ((a.DisabledPopChange) / (a.DisabledPop_ACS2014 - a.DisabledPopChange))*100 
WHEN a.DisabledPopChange = 0 THEN 0 
ELSE 100 END AS DisabledPopPctChange,
a.DisabledPopSOT, 

a.MinorityPopulation_ACS2014,
Case When a.MinorityPopulation_ACS2014 - a.MinorityPopChange<0 Then 0 Else a.MinorityPopulation_ACS2014 - a.MinorityPopChange END  AS MinorityPop_2000, 
CASE When (a.MinorityPopulation_ACS2014 - a.MinorityPopChange)<0 Then a.MinorityPopulation_ACS2014 Else a.MinorityPopChange END as MinorityPopChange,
CASE 
WHEN a.MinorityPopChange < 0 AND a.MinorityPopulation_ACS2014 = 0 THEN -100
WHEN a.MinorityPopulation_ACS2014 > 0 AND (a.MinorityPopulation_ACS2014 - a.MinorityPopChange)>0 THEN ((a.MinorityPopChange) / (a.MinorityPopulation_ACS2014 - a.MinorityPopChange))*100 
WHEN a.MinorityPopChange = 0 THEN 0 
ELSE 100 END AS MinorityPopPctChange,
a.MinorityPopSOT, 
b.COCFLAG_2017,
b.PDA_Flag,
b.PDA_Share,
b.HDI_Flag,
b.HOA_Flag,
b.TPA_Flag,
b.TPA_Share,
b.OpportunityArea_Class,
b.Opportunity_Flag,
b.Walkability_Mean_06,
b.AMI_80,
b.Employment_Density_2010,

b.SHAPE
INTO EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2
FROM            
EJ_Select_Variables_ACS2014_Census2000_Compare_Revised AS a INNER JOIN ACS_2014_ALL_COC_DATA_TRACTS AS b ON a.GEOID = b.GEOID