deeejay

SQL - Cerner Procedure and Diagnosis queries

Jan 29th, 2016
431
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.20 KB | None | 0 0
  1. PROCEDURE
  2. ===
  3. 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
  4. FROM encounter e1
  5. 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
  6. LEFT JOIN PROCEDURE proc ON e1.encntr_id = proc.encntr_id AND proc.active_ind = 1 AND proc.end_effective_dt_tm > SYSDATE
  7. LEFT JOIN nomenclature icd ON proc.nomenclature_id = icd.nomenclature_id AND icd.active_ind = 1 --AND icd.source_vocabulary_cd = 1231
  8. LEFT JOIN proc_prsnl_reltn ppr ON proc.procedure_id = ppr.procedure_id AND ppr.proc_prsnl_reltn_cd = 1209 --surgeon
  9. LEFT JOIN prsnl pr ON ppr.prsnl_person_id = pr.person_id
  10. LEFT JOIN code_value cv ON ppr.proc_prsnl_reltn_cd = cv.code_value AND cv.active_ind = 1
  11. 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
  12. LEFT JOIN prsnl pr2 ON epr.prsnl_person_id = pr2.person_id
  13. LEFT JOIN code_value cv2 ON epr.encntr_prsnl_r_cd = cv2.code_value AND cv2.active_ind = 1
  14. WHERE 1=1
  15. --AND e1.organization_id IN (123456, 654321)
  16. AND e1.encntr_id = 123456;
  17.  
  18. -- 1116 admitting
  19. -- 1119 attending
  20. -- 1209 surgeon
  21.  
  22. DIAGNOSIS
  23. ===
  24. 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
  25. FROM encounter e1
  26. 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
  27. LEFT JOIN diagnosis d ON e1.encntr_id = d.encntr_id AND d.active_ind = 1
  28. LEFT JOIN nomenclature n ON d.nomenclature_id = n.nomenclature_id AND n.active_ind = 1 AND n.end_effective_dt_tm > SYSDATE
  29. LEFT JOIN code_value diag_type ON d.diag_type_cd = diag_type.code_value AND diag_type.active_ind = 1
  30. LEFT JOIN code_value poa ON d.present_on_admit_cd = poa.code_value AND poa.active_ind = 1
  31. WHERE e1.encntr_id = 123456
  32. ORDER BY d.diag_type_cd ASC, d.diag_priority ASC;
Advertisement
Add Comment
Please, Sign In to add comment