Created
December 16, 2016 22:25
-
-
Save Keareys/4560039ff6c75d6f66ccae6026a894ed to your computer and use it in GitHub Desktop.
Correspondence between ACS 2014 and Census 2000
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| ACS.GEOID, ACS.county, | |
| ACS.TotalPopulation AS TotalPop_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' THEN (ACS.TotalPopulation - C2k.TotalPopulation) | |
| WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.TotalPopulation - C2k.TotalPopulation) | |
| ELSE (ACS.TotalPopulation - Round((Rel.POPPCT00 / 100) * C2K.TotalPopulation, 0)) END AS PopChange, | |
| ACS.Total_White_Alone AS TotalWhitePop_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' THEN (ACS.Total_White_Alone - C2k.White_Alone) | |
| WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.Total_White_Alone - C2k.White_Alone) | |
| ELSE (ACS.Total_White_Alone - Round((Rel.POPPCT00 / 100) * C2K.White_Alone, 0)) END AS White_Alone_PopChange, | |
| ACS.Total_Black_Alone AS TotalBlackPop_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' THEN (ACS.Total_Black_Alone - C2k.Black_Alone) | |
| WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.Total_Black_Alone - C2k.Black_Alone) | |
| ELSE (ACS.Total_Black_Alone - Round((Rel.POPPCT00 / 100) * C2K.Black_Alone, 0)) END AS Black_Alone_PopChange, | |
| ACS.Total_Hispanic_Latino AS Total_Hispanic_Latino_Pop_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' THEN (ACS.Total_Hispanic_Latino - C2k.Hispanic_Latino) | |
| WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.Total_Hispanic_Latino - C2K.Hispanic_Latino) | |
| ELSE (ACS.Total_Hispanic_Latino - Round((Rel.POPPCT00 / 100) * C2k.Hispanic_Latino, 0)) END AS Hispanic_Latino_PopChange, | |
| ACS.Total_Asian_Pacific_Islander AS TotalAsian_Pacific_IslanderPop_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' THEN (ACS.Total_Asian_Pacific_Islander - (C2k.Asian_Pacific_Islander)) | |
| WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.Total_Asian_Pacific_Islander - (Asian_Pacific_Islander)) | |
| ELSE (ACS.Total_Asian_Pacific_Islander - Round((Rel.POPPCT00 / 100) * (C2k.Asian_Pacific_Islander), 0)) END AS Asian_Pacific_Islander_PopChange, | |
| --Add other variables in this section | |
| ACS.POP_ZVHHS AS POP_ZVHHS_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' THEN (ACS.POP_ZVHHS - (C2k.POP_ZVHHS)) | |
| WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.POP_ZVHHS - (C2k.POP_ZVHHS)) | |
| ELSE (ACS.POP_ZVHHS - Round((Rel.POPPCT00 / 100) * (C2k.POP_ZVHHS), 0)) END AS POP_ZVHHS_Change, | |
| ACS.POP_LEP AS POP_LEP_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' THEN (ACS.POP_LEP - (C2k.POP_LEP)) | |
| WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.POP_LEP - (C2k.POP_LEP)) | |
| ELSE (ACS.POP_LEP - Round((Rel.POPPCT00 / 100) * (C2k.POP_LEP), 0)) END AS POP_LEP_Change, | |
| ACS.SPFAM AS SPFAM_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' THEN (ACS.SPFAM - (C2k.SPFAM)) | |
| WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.SPFAM - (C2k.SPFAM)) | |
| ELSE (ACS.SPFAM - Round((Rel.POPPCT00 / 100) * (C2k.SPFAM), 0)) END AS SPFAM_Change, | |
| ACS.POP_HUS_RENT50 AS POP_HUS_RENT50_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' THEN (ACS.POP_HUS_RENT50 - (C2k.POP_HUS_RENT50)) | |
| WHEN (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.POP_HUS_RENT50 - (C2k.POP_HUS_RENT50)) | |
| ELSE (ACS.POP_HUS_RENT50 - Round((Rel.POPPCT00 / 100) * (C2k.POP_HUS_RENT50), 0)) END AS POP_HUS_RENT50_Change, | |
| --End of additional section | |
| ACS.PopAge65plus AS Pop65plus_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' OR (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.PopAge65plus - C2k.Pop65plus) | |
| ELSE (ACS.PopAge65plus - Round((Rel.POPPCT00 / 100) * C2K.Pop65plus, 0)) END AS Pop65PlusChange, | |
| ACS.Age65plusSOT, | |
| ACS.Veterans AS Veterans_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' OR (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.Veterans - C2k.Veterans) | |
| ELSE (ACS.Veterans - Round((Rel.POPPCT00 / 100) * C2K.Veterans, 0)) END AS VeteransChange, | |
| ACS.VeteransSOT, | |
| ACS.LowIncomePop AS LowIncomePop_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' OR (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.LowIncomePop - C2k.LowIncomePopulation) | |
| ELSE (ACS.LowIncomePop - Round((Rel.POPPCT00 / 100) * C2K.LowIncomePopulation, 0)) | |
| END AS LowIncomePopChange, | |
| ACS.LowIncPopSOT, | |
| ACS.DisabledPop AS DisabledPop_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' OR (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.DisabledPop - C2k.DisabledPop) | |
| ELSE (ACS.DisabledPop - Round((Rel.POPPCT00 / 100) * C2K.DisabledPop, 0)) END AS DisabledPopChange, | |
| ACS.DisabledPopSOT, | |
| ACS.MinorityPopulation AS MinorityPopulation_ACS2014, | |
| CASE WHEN rel.PART00 = 'W' OR (rel.PART00 = 'P' AND rel.POPPCT00 = 0) | |
| THEN (ACS.MinorityPopulation - C2k.Minority_Population) | |
| ELSE (ACS.MinorityPopulation - Round((Rel.POPPCT00 / 100) * C2K.Minority_Population, 0)) END AS MinorityPopChange, | |
| ACS.MinorityPopSOT | |
| FROM | |
| ACS_2014_EJ_Selected_Variables AS ACS INNER JOIN | |
| Census_Tract_Relationship_Correspondence AS rel ON ACS.GEOID = rel.GEOID10 LEFT OUTER JOIN | |
| Census2000_EJ_SelectedVariables_Revised AS C2K ON rel.GEOID00 = C2K.GEOID |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment