Sal statement that builds the EJ Map series data tables.

EJ_2016_Databuilder.sql

--Key Variables for EJ Data
--Main COC Database Table: [Analysis].[ACS_2014_ALL_COC_DATA_TRACTS]
--Map 1a CoCs DONE
Go
create view EJ_2016.Map1a as
select 
GEOID,  
[COCFLAG_2017] 
From [Analysis].[ACS_2014_ALL_COC_DATA_TRACTS]
Go
--Map 1b Level of Disadvantage DONE
Create view EJ_2016.Map1b as
select 
GEOID, 
Disadvantage_Level 
From [Analysis].Disadvantage_Level
Go
--Map 2a LI and M Pop in 2000 DONE
create view EJ_2016.Map2a as
select 
GEOID,
[TotalPopulation],
[LowIncomePopulation], 
[Share_LowIncome_Population]
[Minority_Population], 
[Share_MinorityPopulation]
From [Analysis].[CENSUS2000_EJ_SELECTEDVARIABLES_REVISED]
Go
--Map 2b LI and M Pop in 2014 DONE
create view EJ_2016.Map2b as
select 
GEOID,
[TotalPopulation],
[LowIncomePop],
[LowIncPopSOT],
[MinorityPopulation],
[MinorityPopSOT]
From [Analysis].[ACS_2014_EJ_Selected_Variables]
Go
--Map2b LI and M Pop in 2014 (Uses old data collected by Stella DONE
create view EJ_2016.Map2b_deprecated as
select
GEOID,
[TOT_POP],
[POP_BELOW200],
[PCT_BELOW200],
[POP_MINORITY],
[PCT_MINORITY]
From [Analysis].[ACS_2014_ALL_COC_DATA_TRACTS]
Go
--Map 2c Change in LI Pop 2000-2014 DONE
create view EJ_2016.Map2c as
select
GEOID,
[TotalPop_ACS2014],
[Pop_2000],
[PopChange],
[LowIncomePop_ACS2014],
[LowIncomePop_2000],
[LowIncomePopChange],
[LowIncomePopPctChange],
[LowIncPopSOT]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go
--Map 2d Change in M Pop 2000-2014 DONE
create view EJ_2016.Map2d as
select
GEOID,
[TotalPop_ACS2014],
[Pop_2000],
[PopChange],
[MinorityPopulation_ACS2014],
[MinorityPop_2000],
[MinorityPopChange],
[MinorityPopPctChange],
[MinorityPopSOT]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go
--Map 3a Change in White Pop 2000-2014 DONE
create view EJ_2016.Map3a as
select
GEOID,
[TotalPop_ACS2014],
[Pop_2000],
[PopChange],
[White_Alone_2014],
[White_Alone_2000],
[WhitePopChange],
[WhiteAlonePopPctChange]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go
--Map 3a Change in Black Pop 2000-2014 DONE
create view EJ_2016.Map3b as
select
GEOID,
[TotalPop_ACS2014],
[Pop_2000],
[PopChange],
[Black_Alone_2014],
[Black_Alone_2000],
[BlackPopChange],
[BlackAlonePopPctChange]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go
--Map 3c Change in Asian and Pacific Islander Pop 2000-2014 DONE
create view EJ_2016.Map3c as
select
GEOID,
[TotalPop_ACS2014],
[Pop_2000],
[PopChange],
[Asian_Pacific_Islander_2014],
[Asian_Pacific_Islander_2000],
[Asian_Pacific_IslanderPopChange],
[AsianPacificPopPctChange]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go
--Map 3d Change in Hispanic Pop 2000-2014 DONE
create view EJ_2016.Map3d as
select
GEOID,
[TotalPop_ACS2014],
[Pop_2000],
[PopChange],
[Hispanic_Alone_2014],
[Hispanic_Alone_2000],
[Hispanic_LatinoPopChange],
[HispanicLatinoPopPctChange]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go
--Map 3d Change in Hispanic Pop 2000-2014 DONE
create view EJ_2016.Map3d as
select
GEOID,
[TotalPop_ACS2014],
[Pop_2000],
[PopChange],
[Hispanic_Alone_2014],
[Hispanic_Alone_2000],
[Hispanic_LatinoPopChange],
[HispanicLatinoPopPctChange]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go
--Map 4a Minority Population 2000 DONE
create view EJ_2016.Map4a as
select
GEOID,
[TotalPopulation],
[Minority_Population],
[Share_MinorityPopulation]
From [Analysis].[CENSUS2000_EJ_SELECTEDVARIABLES_REVISED]
Go
--Map 4b Minority Population 2014 DONE
create view EJ_2016.Map4b as
select
GEOID,
[TotalPopulation],
[MinorityPopulation],
[MinorityPopSOT]
From [Analysis].[ACS_2014_EJ_Selected_Variables]
Go
--Map 4c Change in Minority Population 2000-2014 DONE
create view EJ_2016.Map4c as
select
GEOID,
[TotalPop_ACS2014],
[Pop_2000],
[PopChange],
[MinorityPopulation_ACS2014],
[MinorityPop_2000],
[MinorityPopChange],
[MinorityPopPctChange],
[MinorityPopSOT]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go
--Map 4d Low-Income Population 2000 DONE
create view EJ_2016.Map4d as
select 
GEOID,
[TotalPopulation],
[LowIncomePopulation],
[Share_LowIncome_Population]
From [Analysis].[CENSUS2000_EJ_SELECTEDVARIABLES_REVISED]
Go
--Map 4e Low-Income Population 2014 DONE
create view EJ_2016.Map4e as
select 
GEOID,
[TotalPopulation],
[LowIncomePop],
[LowIncPopSOT]
From [Analysis].[ACS_2014_EJ_Selected_Variables]
Go
--Map 4f Change in LI Population 2000-2014 DONE
create view EJ_2016.Map4f as
select 
GEOID,
[TotalPop_ACS2014]
[Pop_2000],
[PopChange],
[TotalPopPctChange],
[LowIncomePop_ACS2014],
[LowIncomePop_2000],
[LowIncomePopChange],
[LowIncomePopPctChange],
[LowIncPopSOT]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go
--Map 4g Senior Population 2000 DONE
create view EJ_2016.Map4g as
select 
GEOID,
[TotalPopulation],
[Pop65plus],
[Pop65plus]/[TotalPopulation] as [Age65plusSOT_2000]
From [Analysis].[CENSUS2000_EJ_SELECTEDVARIABLES_REVISED]
Go
--Map 4h Senior Population 2014 DONE
create view EJ_2016.Map4h as
select 
GEOID,
[TotalPopulation],
[PopAge65plus],
[Age65plusSOT] as [Age65plusSOT_2014]
From [Analysis].[ACS_2014_EJ_Selected_Variables]
Go
--Map 4i Change in Senior Population 2000-2014 DONE
create view EJ_2016.Map4i as
select 
GEOID,
[TotalPop_ACS2014]
[Pop_2000],
[PopChange],
[TotalPopPctChange],
[Pop65plus_ACS2014],
[Pop65plus_2000],
[Pop65PlusChange],
[Pop65PlusPctChange]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go


--Start Here
--Map 4xx Senior Population 2000 DONE
create view EJ_2016.Map4xx as
select 
GEOID,
[TotalPopulation],
[Pop65plus],
[Pop65plus]/[TotalPopulation] as [Age65plusSOT_2000]
From [Analysis].[CENSUS2000_EJ_SELECTEDVARIABLES_REVISED]
Go
--Map 4xx Senior Population 2014 DONE
create view EJ_2016.Map4 as
select 
GEOID,
[TotalPopulation],
[PopAge65plus],
[Age65plusSOT] as [Age65plusSOT_2014]
From [Analysis].[ACS_2014_EJ_Selected_Variables]
Go
--Map 4xx Change in Senior Population 2000-2014 DONE
create view EJ_2016.Map4i as
select 
GEOID,
[TotalPop_ACS2014]
[Pop_2000],
[PopChange],
[TotalPopPctChange],
[Pop65plus_ACS2014],
[Pop65plus_2000],
[Pop65PlusChange],
[Pop65PlusPctChange]
From [Analysis].[EJ_Select_Variables_ACS2014_Census2000_Compare_Update_v2]
Go