CLTST SQL Queries

CLTST_Queries_Demographics.sql

/* DEMOGRAPHICS */

SELECT DISTINCT pat.pat_id, pat.pat_mrn_id AS mrn, pat.pat_last_name AS last_name, pat.pat_first_name AS first_name,
substr(pat.pat_middle_name,1,1) AS mid_initial, pat2.maiden_name AS maiden_name, pat.birth_date AS dob, c.name AS county,
s.name AS state, pat.zip AS zip, enc.contact_date AS encounter_date, floor(months_between(enc.contact_date, pat.birth_date)/12) AS age,

(CASE WHEN sex.abbr='oth' THEN 'o' ELSE sex.abbr END) AS sex, 

(SELECT listagg(race.name, ',') WITHIN GROUP (ORDER BY pr.line)
FROM clarity.patient_race pr, clarity.zc_patient_race race
WHERE pr.pat_id=pat.pat_id
AND race.patient_race_c=pr.patient_race_c) AS race
 
FROM clarity.pat_enc enc
INNER JOIN clarity.patient pat ON enc.pat_id=pat.pat_id
INNER JOIN clarity.patient_2 pat2 ON pat.pat_id=pat2.pat_id
LEFT OUTER JOIN clarity.zc_county c ON pat.county_c=c.county_c
INNER JOIN clarity.zc_state s ON pat.state_c=s.state_c
INNER JOIN clarity.zc_sex_2 sex ON pat.sex_c=sex.sex_2_c;

CLTST_Queries_Diagnoses.sql

/* DIAGNOSES */

SELECT DISTINCT p.pat_id, p.pat_mrn_id, 'enc' AS dxtype, eci.code, 1 AS dx

FROM clarity.patient p
INNER JOIN clarity.pat_enc pe ON p.pat_id=pe.pat_id
INNER JOIN clarity.pat_enc_dx ped ON pe.pat_enc_csn_id=ped.pat_enc_csn_id
INNER JOIN clarity.clarity_edg edg ON ped.dx_id = edg.dx_id
INNER JOIN clarity.edg_current_icd10 eci ON edg.dx_id = eci.dx_id

WHERE REGEXP_LIKE(eci.code, '^(J)');

CLTST_Queries_FlowSheet.sql

/* FLOWSHEET/VITALS */

SELECT DISTINCT p.pat_id, p.pat_mrn_id AS MRN, to_char(ipfm.recorded_time,'YYYY-mm-dd HH24:MI:SS') AS recorded_time,
ipflog.disp_name AS display_name, ipfm.meas_value AS vital_value
 
FROM clarity.ip_flwsht_meas ipfm   
INNER JOIN clarity.ip_flwsht_rec ipfr ON ipfr.fsd_id=ipfm.fsd_id
LEFT OUTER JOIN clarity.ip_flo_gp_data ipflog ON ipfm.flo_meas_id=ipflog.flo_meas_id
INNER JOIN clarity.pat_enc pe ON pe.inpatient_data_id=ipfr.inpatient_data_id
INNER JOIN clarity.patient p ON pe.pat_id=p.pat_id   
 
WHERE ipfm.flo_meas_id IN ('5','11','14')
AND ipfm.meas_value IS NOT NULL;

CLTST_Queries_Medications.sql

/* MEDICATIONS */

(SELECT DISTINCT p.pat_id, p.pat_mrn_id, pe.pat_enc_csn_id, om.order_med_id, om.start_date, om.end_date,
cm.generic_name AS med_name, pc.name AS pharm_class, psc.name AS pharm_subclass, om.order_status_c
 
FROM clarity.patient p
INNER JOIN clarity.pat_enc_hsp pe ON p.pat_id=pe.pat_id
INNER JOIN clarity.order_med om ON pe.pat_enc_csn_id=om.pat_enc_csn_id        
INNER JOIN clarity.clarity_medication cm ON om.medication_id=cm.medication_id
LEFT OUTER JOIN clarity.mar_admin_info mai ON om.order_med_id=mai.order_med_id
INNER JOIN clarity.zc_pharm_class pc ON cm.pharm_class_c=pc.pharm_class_c
INNER JOIN clarity.zc_pharm_subclass psc ON cm.pharm_subclass_c=psc.pharm_subclass_c
 
WHERE om.order_status_c <> 4 /*not canceled*/
AND om.order_class_c <> 3 /*no historical meds*/
AND mai.mar_action_c IN (1,6,7,9,12,13,101,104,105,115,116,118,119,131))
 
UNION

(SELECT DISTINCT p.pat_id, p.pat_mrn_id, pe.pat_enc_csn_id, om.order_med_id, om.start_date, om.end_date,
cm.generic_name AS med_name, pc.name AS pharm_class, psc.name AS pharm_subclass, om.order_status_c
 
FROM clarity.patient p
INNER JOIN clarity.pat_enc_hsp pe ON p.pat_id=pe.pat_id
INNER JOIN clarity.order_med om ON pe.pat_enc_csn_id=om.pat_enc_csn_id        
INNER JOIN clarity.order_medmixinfo omi ON om.order_med_id=omi.order_med_id
LEFT OUTER JOIN clarity.mar_admin_info mai ON om.order_med_id=mai.order_med_id
INNER JOIN clarity.clarity_medication cm ON omi.medication_id=cm.medication_id
INNER JOIN clarity.zc_pharm_class pc ON cm.pharm_class_c=pc.pharm_class_c
INNER JOIN clarity.zc_pharm_subclass psc ON cm.pharm_subclass_c=psc.pharm_subclass_c
 
WHERE om.order_status_c <> 4 /*not canceled*/
AND om.order_class_c <> 3 /*no historical meds*/
AND mai.mar_action_c  IN (1,6,7,9,12,13,101,104,105,115,116,118,119,131));