View medicare.qc_enrl_bene

Overview for medicare.qc_enrl_bene

Joined data for beneficiaries and enrollments

Join of tables: enrollments natural join beneficiaries

SQL/DDL Statement

CREATE view medicare.qc_enrl_bene AS SELECT *,
(CASE
		  WHEN dob IS NULL THEN 'MISSING'
		  WHEN dob_latest IS NOT NULL THEN 'AMBIGUOUS'
		  ELSE 'CONSISTENT'
		END) AS consistent_dob,
(CASE
		  WHEN dod IS NULL THEN 'NONE'
		  WHEN dod_earliest IS NOT NULL THEN 'AMBIGUOUS'
		  ELSE 'CONSISTENT'
		END) AS consistent_dod,
(CASE
		  WHEN sex LIKE '%,%' THEN 'AMBIGUOUS'
		  ELSE 'CONSISTENT'
		END) AS consistent_sex,
(CASE
		  WHEN race LIKE '%,%' THEN 'AMBIGUOUS'
		  ELSE 'CONSISTENT'
		END) AS consistent_race 
FROM medicare.enrollments natural join medicare.beneficiaries
;

COMMENT ON view medicare.qc_enrl_bene 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.791600';

---

Columns:

Column Name	Column Type	Datatype
age	computed	string
age_min	computed	string
all_enrollment_years	computed	INT[]
bene	generated	HLL_HASHVAL
bene_id		string
buyin	generated	boolean
buyin_cvg_count	computed	string
buyin_indicators	computed	string
consistent_dob	computed	VARCHAR(12)
consistent_dod	computed	VARCHAR(12)
consistent_race	computed	VARCHAR(12)
consistent_sex	computed	VARCHAR(12)
curec	computed	string
died	computed	string
discrepancies	computed	string
dob	computed	string
dob_latest	computed	string
dod	computed	string
dod_earliest	computed	string
dual	generated	boolean
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
first_enrollment_year	computed	string
hmo	generated	boolean
hmo_cvg_count	computed	string
hmo_indicators	computed	string
last_enrollment_year	computed	string
number_of_gap_years	generated	INT
orec	computed	string
race	computed	string
race_rti	computed	string
record	computed	string
residence_counties	computed	string
residence_county	computed	string
sex	computed	string
ssa2	computed	string
ssa2_list	computed	string
ssa3	computed	string
ssa3_list	computed	string
state		string
state_count	computed	string
state_iso	computed	char(5)
year		string
yob	computed	string
yob_latest	computed	string
zip	computed	string
zips	computed	string