Materialized view medicare._ps

Overview for medicare._ps

internal intermediate view adding some data conversions

Transformed from medicare.ps

SQL/DDL Statement

CREATE materialized view medicare._ps AS SELECT *,
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)
  END
ELSE
  (SELECT public.zip_to_fips3(year, zip))
END AS fips3,
CASE WHEN ssa3 is NOT NULL THEN
    False
  ELSE
    (SELECT NOT public.is_zip_to_fips_exact(year, zip))
END AS fips3_is_approximated,
CASE WHEN year > 2010 THEN (SELECT string_agg(distinct fips3::varchar, ',') from public.ssa as s where ps.ssa3 = s.ssa3 and ps.year = s.year and ps.fips2 = s.fips2)
    ELSE (SELECT string_agg(distinct fips3::varchar, ',') from public.ssa as s where ps.ssa3 = s.ssa3 and 2003 = s.year and ps.fips2 = s.fips2) END AS fips3_list,
CASE WHEN age is not NULL THEN (year - age) ELSE NULL END as yob 
FROM medicare.ps
;

COMMENT ON materialized view medicare._ps IS 'CREATED BY Dorieh: {"version": "0.2.3", "url": "https://github.com/NSAPH-Data-Platform/dorieh", "commit": "bf4644403297aaf478f6a42717508dde55da409a"}. Created at 2024-08-12 13:31:09.766081';

---

Columns:

Column Name	Column Type	Datatype
age	copied	INT
bene_id	copied	string
buyin_cvg_count	copied	INT
buyin_indicators	copied	CHAR(1)[12]
curec	copied	INT
dob	copied	date
dod	copied	date
dod_ndi	copied	DATE
file	copied	string
fips2	copied	string
fips3	computed	string
fips3_is_approximated	computed	string
fips3_list	computed	string
hmo_cvg_count	copied	INT
hmo_indicators	copied	CHAR(1)[12]
orec	copied	INT
race	copied	VARCHAR
race_rti	copied	string
record	copied	string
sex	copied	VARCHAR
ssa2	copied	string
ssa3	copied	string
state	copied	string
year	copied	string
yob	computed	string
zip	copied	INT
zip4	copied	INT