Table medicare.admissions

Overview for medicare.admissions

Medicare inpatient admissions

Transformed from medicare.ip

Primary Key: bene_id, year, state, admission_date, discharge_date

SQL/DDL Statement

CREATE table medicare.admissions AS SELECT bene_id,
file,
record,
year,
state,
ssa2,
fips2,
zip,
zip4,
admission_date_raw,
discharge_date_raw,
admission_date,
discharge_date,
adm_day_of_week,
dschrgcd,
dschrg_dstntn_cd,
admsn_type_cd,
src_admsn_cd,
btrim(diag1) As primary_diagnosis,
array_remove(ARRAY[
btrim(btrim(diag1)),
btrim(diag2),
btrim(diag3),
btrim(diag4),
btrim(diag5),
btrim(diag6),
btrim(diag7),
btrim(diag8),
btrim(diag9),
btrim(diag10),
btrim(diag11),
btrim(diag12),
btrim(diag13),
btrim(diag14),
btrim(diag15),
btrim(diag16),
btrim(diag17),
btrim(diag18),
btrim(diag19),
btrim(diag20),
btrim(diag21),
btrim(diag22),
btrim(diag23),
btrim(diag24),
btrim(diag25)
], NULL) As diagnoses,
los_day_cnt,
drg_price_amt,
drg_outlier_pmt_amt,
pass_thru_amt,
mdcr_pmt_amt,
bene_blood_ddctbl_amt,
bene_prmry_pyr_amt,
bene_ip_ddctbl_amt,
bene_pta_coinsrnc_amt 
FROM medicare.ip
WHERE 1 = 0;
ALTER table medicare.admissions ADD COLUMN quality VARCHAR(12) DEFAULT 'PASS';
ALTER table medicare.admissions ADD COLUMN admission_year INT GENERATED ALWAYS AS (EXTRACT (YEAR FROM admission_date)) STORED;
ALTER table medicare.admissions ADD COLUMN state_iso CHAR(5) GENERATED ALWAYS AS ('US-' || state)  STORED;
ALTER table medicare.admissions ADD COLUMN bene HLL_HASHVAL GENERATED ALWAYS AS (hll_hash_text(bene_id)) STORED;
ALTER table medicare.admissions ADD COLUMN pd_hll_hash HLL_HASHVAL GENERATED ALWAYS AS (hll_hash_text(primary_diagnosis)) STORED;
ALTER table medicare.admissions ADD COLUMN icd_hll HLL GENERATED ALWAYS AS (hll_arr_agg(diagnoses)) STORED;
ALTER table medicare.admissions ADD PRIMARY KEY (bene_id, year, state, admission_date, discharge_date);
ALTER table medicare.admissions ADD CONSTRAINT admissions_to_enrollments FOREIGN KEY (bene_id, year, state) REFERENCES medicare.enrollments (bene_id, year, state);

CREATE TABLE  medicare_audit.admissions (
	quality VARCHAR(12) DEFAULT 'PASS',
	admission_year INT GENERATED ALWAYS AS (EXTRACT (YEAR FROM admission_date)) STORED,
	diagnoses VARCHAR(12)[10],
	state_iso CHAR(5) GENERATED ALWAYS AS ('US-' || state)  STORED,
	bene HLL_HASHVAL GENERATED ALWAYS AS (hll_hash_text(bene_id)) STORED,
	pd_hll_hash HLL_HASHVAL GENERATED ALWAYS AS (hll_hash_text(primary_diagnosis)) STORED,
	icd_hll HLL GENERATED ALWAYS AS (hll_arr_agg(diagnoses)) STORED,
	bene_id VARCHAR,
	file VARCHAR,
	record VARCHAR,
	year INT,
	state VARCHAR,
	ssa2 VARCHAR,
	fips2 VARCHAR,
	zip INT,
	zip4 INT,
	admission_date_raw VARCHAR,
	discharge_date_raw VARCHAR,
	admission_date DATE,
	discharge_date DATE,
	adm_day_of_week INT,
	dschrgcd CHAR(1),
	dschrg_dstntn_cd CHAR(2),
	admsn_type_cd CHAR(1),
	src_admsn_cd CHAR(1),
	primary_diagnosis VARCHAR,
	los_day_cnt INT,
	drg_price_amt NUMERIC,
	drg_outlier_pmt_amt NUMERIC,
	pass_thru_amt NUMERIC,
	mdcr_pmt_amt NUMERIC,
	bene_blood_ddctbl_amt NUMERIC,
	bene_prmry_pyr_amt NUMERIC,
	bene_ip_ddctbl_amt NUMERIC,
	bene_pta_coinsrnc_amt NUMERIC,
	REASON VARCHAR(16),
	REFCTID TID,
	recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
);
CREATE INDEX  admissions_REFCTID_idx ON medicare_audit.admissions USING BTREE (REFCTID) ;

CREATE OR REPLACE FUNCTION medicare.validate_admissions() RETURNS TRIGGER 
-- Validate foreign key for medicare.admissions
AS $body$
    DECLARE rctid tid;
    BEGIN
        IF (NEW.bene_id IS NULL 
				OR NEW.year IS NULL 
				OR NEW.state IS NULL 
				OR NEW.admission_date IS NULL 
				OR NEW.discharge_date IS NULL ) THEN
            INSERT INTO medicare_audit.admissions 
                (quality,diagnoses,bene_id,file,record,year,state,ssa2,fips2,zip,zip4,admission_date_raw,discharge_date_raw,admission_date,discharge_date,adm_day_of_week,dschrgcd,dschrg_dstntn_cd,admsn_type_cd,src_admsn_cd,primary_diagnosis,los_day_cnt,drg_price_amt,drg_outlier_pmt_amt,pass_thru_amt,mdcr_pmt_amt,bene_blood_ddctbl_amt,bene_prmry_pyr_amt,bene_ip_ddctbl_amt,bene_pta_coinsrnc_amt, REASON) 
                VALUES (NEW.quality,NEW.diagnoses,NEW.bene_id,NEW.file,NEW.record,NEW.year,NEW.state,NEW.ssa2,NEW.fips2,NEW.zip,NEW.zip4,NEW.admission_date_raw,NEW.discharge_date_raw,NEW.admission_date,NEW.discharge_date,NEW.adm_day_of_week,NEW.dschrgcd,NEW.dschrg_dstntn_cd,NEW.admsn_type_cd,NEW.src_admsn_cd,NEW.primary_diagnosis,NEW.los_day_cnt,NEW.drg_price_amt,NEW.drg_outlier_pmt_amt,NEW.pass_thru_amt,NEW.mdcr_pmt_amt,NEW.bene_blood_ddctbl_amt,NEW.bene_prmry_pyr_amt,NEW.bene_ip_ddctbl_amt,NEW.bene_pta_coinsrnc_amt, 'PRIMARY KEY');
            RETURN NULL;
        END IF;
        IF NOT EXISTS (
            SELECT FROM medicare.enrollments as t
            WHERE
                NEW.bene_id = t.bene_id
				AND NEW.year = t.year
				AND NEW.state = t.state 
        ) THEN
            INSERT INTO medicare_audit.admissions 
                (quality,diagnoses,bene_id,file,record,year,state,ssa2,fips2,zip,zip4,admission_date_raw,discharge_date_raw,admission_date,discharge_date,adm_day_of_week,dschrgcd,dschrg_dstntn_cd,admsn_type_cd,src_admsn_cd,primary_diagnosis,los_day_cnt,drg_price_amt,drg_outlier_pmt_amt,pass_thru_amt,mdcr_pmt_amt,bene_blood_ddctbl_amt,bene_prmry_pyr_amt,bene_ip_ddctbl_amt,bene_pta_coinsrnc_amt, REASON) 
                VALUES (NEW.quality,NEW.diagnoses,NEW.bene_id,NEW.file,NEW.record,NEW.year,NEW.state,NEW.ssa2,NEW.fips2,NEW.zip,NEW.zip4,NEW.admission_date_raw,NEW.discharge_date_raw,NEW.admission_date,NEW.discharge_date,NEW.adm_day_of_week,NEW.dschrgcd,NEW.dschrg_dstntn_cd,NEW.admsn_type_cd,NEW.src_admsn_cd,NEW.primary_diagnosis,NEW.los_day_cnt,NEW.drg_price_amt,NEW.drg_outlier_pmt_amt,NEW.pass_thru_amt,NEW.mdcr_pmt_amt,NEW.bene_blood_ddctbl_amt,NEW.bene_prmry_pyr_amt,NEW.bene_ip_ddctbl_amt,NEW.bene_pta_coinsrnc_amt, 'FOREIGN KEY');
            RETURN NULL;
        END IF;
        IF EXISTS (
            SELECT FROM medicare.admissions as t
            WHERE
                NEW.bene_id = t.bene_id
				AND NEW.year = t.year
				AND NEW.state = t.state
				AND NEW.admission_date = t.admission_date
				AND NEW.discharge_date = t.discharge_date 
        ) THEN
            
            SELECT 
                ctid 
            FROM medicare.admissions AS t 
            WHERE NEW.bene_id = t.bene_id
				AND NEW.year = t.year
				AND NEW.state = t.state
				AND NEW.admission_date = t.admission_date
				AND NEW.discharge_date = t.discharge_date 
            INTO rctid;
INSERT INTO medicare_audit.admissions 
                (quality,diagnoses,bene_id,file,record,year,state,ssa2,fips2,zip,zip4,admission_date_raw,discharge_date_raw,admission_date,discharge_date,adm_day_of_week,dschrgcd,dschrg_dstntn_cd,admsn_type_cd,src_admsn_cd,primary_diagnosis,los_day_cnt,drg_price_amt,drg_outlier_pmt_amt,pass_thru_amt,mdcr_pmt_amt,bene_blood_ddctbl_amt,bene_prmry_pyr_amt,bene_ip_ddctbl_amt,bene_pta_coinsrnc_amt,REFCTID, REASON) 
                VALUES (NEW.quality,NEW.diagnoses,NEW.bene_id,NEW.file,NEW.record,NEW.year,NEW.state,NEW.ssa2,NEW.fips2,NEW.zip,NEW.zip4,NEW.admission_date_raw,NEW.discharge_date_raw,NEW.admission_date,NEW.discharge_date,NEW.adm_day_of_week,NEW.dschrgcd,NEW.dschrg_dstntn_cd,NEW.admsn_type_cd,NEW.src_admsn_cd,NEW.primary_diagnosis,NEW.los_day_cnt,NEW.drg_price_amt,NEW.drg_outlier_pmt_amt,NEW.pass_thru_amt,NEW.mdcr_pmt_amt,NEW.bene_blood_ddctbl_amt,NEW.bene_prmry_pyr_amt,NEW.bene_ip_ddctbl_amt,NEW.bene_pta_coinsrnc_amt,rctid, 'DUPLICATE');
            RETURN NULL;
        END IF;
        RETURN NEW;
    END;
 
$body$ LANGUAGE plpgsql;

CREATE TRIGGER medicare_admissions_validation BEFORE INSERT ON medicare.admissions
        FOR EACH ROW EXECUTE FUNCTION medicare.validate_admissions();
COMMENT ON table medicare.admissions 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.790691';

Columns:

Column Name	Column Type	Datatype
adm_day_of_week	copied	INT
admission_date	copied	DATE
admission_date_raw	copied	VARCHAR
admission_year	generated	INT
admsn_type_cd	copied	CHAR(1)
bene	generated	HLL_HASHVAL
bene_blood_ddctbl_amt	copied	NUMERIC
bene_id	copied	string
bene_ip_ddctbl_amt	copied	NUMERIC
bene_prmry_pyr_amt	copied	NUMERIC
bene_pta_coinsrnc_amt	copied	NUMERIC
diagnoses	computed	string
discharge_date	copied	DATE
discharge_date_raw	copied	VARCHAR
drg_outlier_pmt_amt	copied	NUMERIC
drg_price_amt	copied	NUMERIC
dschrg_dstntn_cd	copied	CHAR(2)
dschrgcd	copied	CHAR(1)
file		string
fips2	copied	string
icd_hll	generated	HLL
los_day_cnt	copied	INT
mdcr_pmt_amt	copied	NUMERIC
pass_thru_amt	copied	NUMERIC
pd_hll_hash	generated	HLL_HASHVAL
primary_diagnosis	computed	string
quality		VARCHAR(12) DEFAULT ‘PASS’
record	copied	string
src_admsn_cd	copied	CHAR(1)
ssa2	copied	string
state	copied	string
state_iso	generated	char(5)
year	computed	INT
zip	copied	INT
zip4	copied	INT