View medicare._enrollments

Overview for medicare._enrollments

Internal intermediate view of beneficiary enrollments into Medicare program, by year and state of enrollment

Aggregated from medicare._ps on On bene_id, year, state

Primary Key: bene_id, year, state

SQL/DDL Statement

CREATE view  medicare._enrollments AS
SELECT
    bene_id,
	year,
	state,
	MAX(age) AS age,
	CASE
		  WHEN MIN(age) <> MAX(age) THEN MIN(age)
		END AS age_min,
	MAX(orec) AS orec,
	MAX(curec) AS curec,
	('US-' || state) AS state_iso,
	MAX(fips2) AS fips2,
	MAX(fips3) AS fips3,
	every(fips3_is_approximated) AS fips3_is_approximated,
	public.validate_zip_fips(MAX(zip), MAX(fips2), MAX(fips3)) AS fips3_valdiated,
	MAX(ssa2) AS ssa2,
	MAX(ssa3) AS ssa3,
	string_agg(distinct ssa2, ',') AS ssa2_list,
	string_agg(distinct ssa3, ',') AS ssa3_list,
	MAX(fips3) AS residence_county,
	string_agg(distinct fips3_list, ',') AS residence_counties,
	(MAX(fips2) || MAX(fips3)) AS fips5,
	MAX(zip) AS zip,
	string_agg(distinct zip::varchar, ',') AS zips,
	MAX(hmo_indicators) AS hmo_indicators,
	MAX(hmo_cvg_count) AS hmo_cvg_count,
	MAX(buyin_indicators) AS buyin_indicators,
	MAX(buyin_cvg_count) AS buyin_cvg_count,
	(SELECT MAX(dual_indicators) FROM medicare.mbsf_d AS d WHERE medicare._ps.year = d.year and medicare._ps.bene_id = d.bene_id) AS dual_indicators,
	(CASE
		   WHEN EXISTS (SELECT * FROM medicare.mbsf_d AS d WHERE medicare._ps.year = d.year and medicare._ps.bene_id = d.bene_id)
		   THEN
		       COALESCE((SELECT MAX(dual_mo) FROM medicare.mbsf_d AS d WHERE medicare._ps.year = d.year and medicare._ps.bene_id = d.bene_id), 0)
		   ELSE NULL
		END) AS dual_cvg_count,
	(
		  SELECT COUNT(distinct state)
		  FROM medicare._ps AS ps2
		  WHERE
		    _ps.bene_id = ps2.bene_id
		    AND _ps.year = ps2.year
		) AS state_count,
	CASE EXTRACT (YEAR from MAX(dod))
		  WHEN year THEN TRUE
		  ELSE FALSE
		END AS died,
	string_agg(distinct FILE, ',') AS file,
	string_agg(distinct RECORD::VARCHAR, ',') AS record
FROM medicare._ps

WHERE bene_id IS NOT NULL AND year IS NOT NULL AND state IS NOT NULL
GROUP BY bene_id,year,state;

COMMENT ON view medicare._enrollments 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.766787';

---

Columns:

Column Name	Column Type	Datatype
age	computed	string
age_min	computed	string
bene_id	grouping	string
buyin_cvg_count	computed	string
buyin_indicators	computed	string
curec	computed	string
died	computed	string
dual_cvg_count	computed	string
dual_indicators	computed	string
file	computed	string
fips2	computed	string
fips3	computed	string
fips3_is_approximated	computed	string
fips3_valdiated	computed	string
fips5	computed	string
hmo_cvg_count	computed	string
hmo_indicators	computed	string
orec	computed	string
record	computed	string
residence_counties	computed	string
residence_county	computed	string
ssa2	computed	string
ssa2_list	computed	string
ssa3	computed	string
ssa3_list	computed	string
state	grouping	string
state_count	computed	string
state_iso	computed	char(5)
year	grouping	string
zip	computed	string
zips	computed	string