Column medicare.enrollments.fips3_valdiated Lineage SVG

medicare.enrollments.fips3_valdiated medicare.enrollments.fips3_valdiated public.validate_zip_fips(MAX(zip), MAX(fips2), MAX(fips3)) medicare._enrollments.fips2 medicare._enrollments.fips2 MAX(fips2) medicare._enrollments.fips2->medicare.enrollments.fips3_valdiated Copied medicare._ps.fips2 medicare._ps.fips2 2 digit state FIPS, see https://en.wikip edia.org/wiki/Federal_Information_Proces sing_Standard_state_code (SELECT fips2 FROM public.us_states as s WHERE s.ssa2 = "state") medicare._ps.fips2->medicare._enrollments.fips2  Aggregated On bene_id, year, state medicare.ps.fips2 medicare.ps.fips2 2 digit state FIPS, see https://en.wikip edia.org/wiki/Federal_Information_Proces sing_Standard_state_code (SELECT fips2 FROM public.us_states as s WHERE s.ssa2 = "state") medicare.ps.fips2->medicare._ps.fips2 Copied medicare._ps.fips3 medicare._ps.fips3 CASE WHEN ssa3 is NOT NULL THEN  CASE WHEN year > 2010 THEN      (SELECT fips3 from public.ssa as s where ps.ssa3 = s.ssa3 and ps.year = s.year and ps.fips2 = s.fips2 ORDER BY 1 DESC LIMIT 1)    ELSE      (SELECT fips3 from public.ssa as s where ps.ssa3 = s.ssa3 and 2003 = s.year and ps.fips2 = s.fips2 ORDER BY 1 DESC LIMIT 1)  ENDELSE  (SELECT public.zip_to_fips3(year, zip))END AS fips3 medicare.ps.fips2->medicare._ps.fips3  Transformed medicare._enrollments.zip medicare._enrollments.zip MAX(zip) medicare._enrollments.zip->medicare.enrollments.fips3_valdiated Copied medicare._ps.zip medicare._ps.zip US Postal ZIP code, the first 5 digits of it numeric → (CASE WHEN {column_name} < 100000 THEN {column_name}::int ELSE ({column_name}/10000)::INT END) character varying → SUBSTRING({column_name} FROM 1 FOR 5)::INT integer → {column_name}::INT medicare._ps.zip->medicare._enrollments.zip  Aggregated On bene_id, year, state medicare.ps.zip medicare.ps.zip US Postal ZIP code, the first 5 digits of it numeric → (CASE WHEN {column_name} < 100000 THEN {column_name}::int ELSE ({column_name}/10000)::INT END) character varying → SUBSTRING({column_name} FROM 1 FOR 5)::INT integer → {column_name}::INT medicare.ps.zip->medicare._ps.zip Copied medicare.ps.zip_parent 8 incoming links (columns) medicare.ps.zip_parent->medicare.ps.zip medicare._enrollments.fips3 medicare._enrollments.fips3 MAX(fips3) medicare._enrollments.fips3->medicare.enrollments.fips3_valdiated Copied medicare._ps.fips3->medicare._enrollments.fips3  Aggregated On bene_id, year, state medicare.ps.ssa3 medicare.ps.ssa3 Social Security Administration (SSA) three digit code for county medicare.ps.ssa3->medicare._ps.fips3  Transformed medicare.ps.ssa3_parent 8 incoming links (columns) medicare.ps.ssa3_parent->medicare.ps.ssa3 medicare.ps.year medicare.ps.year (CASE WHEN "year"::int < 20 THEN (2000 + "year"::int) WHEN (20 < "year"::int AND "year"::int < 100) THEN (1900 + "year"::int) WHEN "year" IS NULL THEN 2000 ELSE "year"::int END) medicare.ps.year->medicare._ps.fips3  Transformed medicare.ps.year_parent 8 incoming links (columns) medicare.ps.year_parent->medicare.ps.year