Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- PROCEDURE
- ===
- SELECT e1.encntr_id, ea.alias AS fin, proc.procedure_id, proc.encntr_id, proc.proc_priority, icd.source_identifier AS proc_code, icd.source_string AS proc_description, cv.display AS physician_role, pr.name_full_formatted, cv2.display AS attending_physician, pr2.name_full_formatted
- FROM encounter e1
- LEFT JOIN encntr_alias ea ON e1.encntr_id = ea.encntr_id AND ea.encntr_alias_type_cd = 1077 AND ea.alias_pool_cd = 706841
- LEFT JOIN PROCEDURE proc ON e1.encntr_id = proc.encntr_id AND proc.active_ind = 1 AND proc.end_effective_dt_tm > SYSDATE
- LEFT JOIN nomenclature icd ON proc.nomenclature_id = icd.nomenclature_id AND icd.active_ind = 1 --AND icd.source_vocabulary_cd = 1231
- LEFT JOIN proc_prsnl_reltn ppr ON proc.procedure_id = ppr.procedure_id AND ppr.proc_prsnl_reltn_cd = 1209 --surgeon
- LEFT JOIN prsnl pr ON ppr.prsnl_person_id = pr.person_id
- LEFT JOIN code_value cv ON ppr.proc_prsnl_reltn_cd = cv.code_value AND cv.active_ind = 1
- LEFT JOIN encntr_prsnl_reltn epr ON e1.encntr_id = epr.encntr_id AND epr.active_ind = 1 AND epr.encntr_prsnl_r_cd = 1119 AND epr.end_effective_dt_tm > SYSDATE
- LEFT JOIN prsnl pr2 ON epr.prsnl_person_id = pr2.person_id
- LEFT JOIN code_value cv2 ON epr.encntr_prsnl_r_cd = cv2.code_value AND cv2.active_ind = 1
- WHERE 1=1
- --AND e1.organization_id IN (123456, 654321)
- AND e1.encntr_id = 123456;
- -- 1116 admitting
- -- 1119 attending
- -- 1209 surgeon
- DIAGNOSIS
- ===
- SELECT e1.encntr_id, ea.alias AS fin, d.diagnosis_id, diag_type.display AS diag_type, d.diag_priority, poa.display AS present_on_admit, n.source_identifier AS diag_code, n.source_string AS diag_description
- FROM encounter e1
- LEFT JOIN encntr_alias ea ON e1.encntr_id = ea.encntr_id AND ea.encntr_alias_type_cd = 1077 AND ea.alias_pool_cd = 706841
- LEFT JOIN diagnosis d ON e1.encntr_id = d.encntr_id AND d.active_ind = 1
- LEFT JOIN nomenclature n ON d.nomenclature_id = n.nomenclature_id AND n.active_ind = 1 AND n.end_effective_dt_tm > SYSDATE
- LEFT JOIN code_value diag_type ON d.diag_type_cd = diag_type.code_value AND diag_type.active_ind = 1
- LEFT JOIN code_value poa ON d.present_on_admit_cd = poa.code_value AND poa.active_ind = 1
- WHERE e1.encntr_id = 123456
- ORDER BY d.diag_type_cd ASC, d.diag_priority ASC;
Advertisement
Add Comment
Please, Sign In to add comment