diagnosis_trigger_view

Materialized: false

Definition

 SELECT c.claim_id,
    dl.diagnosis_code,
    cm.diagnosis_code IS NOT NULL AS is_diagnosis_case_management_trigger,
    sl.diagnosis_code_prefix IS NOT NULL AS is_diagnosis_stop_loss_trigger,
    cci.diagnosis_code IS NOT NULL AS is_chronic_condition_indicator
   FROM claims.claim c
     LEFT JOIN claims.claim_diagnosis cd ON cd.claim_id = c.claim_id
     LEFT JOIN claims.icd_diagnosis_lookup dl USING (icd_diagnosis_id)
     LEFT JOIN claims.icd_diagnosis_stop_loss_trigger sl ON cd.diagnosis_code ~~ (sl.diagnosis_code_prefix || '%'::text)
     LEFT JOIN claims.icd_diagnosis_horizon_blue_cross_case_management_lookup cm ON cm.diagnosis_code = cd.diagnosis_code
     LEFT JOIN claims.hcup_diagnosis_chronic_condition_indicator_lookup cci ON cci.diagnosis_code = cd.diagnosis_code AND cci.release_date = dl.release_date
  WHERE dl.diagnosis_code IS NOT NULL AND (cm.diagnosis_code IS NOT NULL OR sl.diagnosis_code_prefix IS NOT NULL OR cci.diagnosis_code IS NOT NULL);

Column	Data Type	Indexed
claim_id	bigint	No
diagnosis_code	text	No
is_diagnosis_case_management_trigger	boolean	No
is_diagnosis_stop_loss_trigger	boolean	No
is_chronic_condition_indicator	boolean	No