Epic Clarity Data Model Guide

SELECT
    e.PAT_ENC_CSN_ID          AS enctr_id,
    e.PAT_ID                  AS pat_id,
    e.CONTACT_DATE            AS enctr_dt,
    e.ENC_TYPE_C              AS enc_type_c,
    zt.NAME                   AS enc_type_desc,
    e.DEPARTMENT_ID           AS dept_id,
    d.DEPARTMENT_NAME         AS dept_nm,
    dx.ICD10_CODE             AS prim_diag_cd,
    edg.DX_NAME               AS prim_diag_desc,
    s.PROV_NAME               AS rndrng_prvdr_nm,
    s.NPI                     AS rndrng_prvdr_npi
FROM PAT_ENC e
-- Decode encounter type category
LEFT JOIN ZC_ENC_TYPE zt
    ON zt.ENC_TYPE_C = e.ENC_TYPE_C
-- Department
LEFT JOIN CLARITY_DEP d
    ON d.DEPARTMENT_ID = e.DEPARTMENT_ID
-- Primary diagnosis (LINE = 1)
LEFT JOIN PAT_ENC_DX dx
    ON dx.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID
    AND dx.LINE = 1
-- Diagnosis description
LEFT JOIN CLARITY_EDG edg
    ON edg.DX_ID = dx.DX_ID
-- Rendering provider
LEFT JOIN CLARITY_SER s
    ON s.PROV_ID = e.VISIT_PROV_ID
WHERE e.CONTACT_DATE >= DATEADD(month, -12, GETDATE())
    AND e.ENC_CLOSED_YN = 'Y'
ORDER BY e.CONTACT_DATE DESC;

SELECT
    e.PAT_ENC_CSN_ID              AS enctr_id,
    e.PAT_ID                      AS pat_id,
    h.HOSP_ADMSN_TIME             AS admit_ts,
    h.HOSP_DISCHRG_TIME           AS dschrg_ts,
    DATEDIFF(day,
        h.HOSP_ADMSN_TIME,
        h.HOSP_DISCHRG_TIME)      AS los_days,
    zd.NAME                       AS dschrg_dispo_desc,
    h.DRG_ID                      AS drg_cd,
    h.DRG_MPI_CODE                AS ms_drg_cd,
    p.PAT_MRN_ID                  AS mrn,
    p.BIRTH_DATE                  AS pat_dob,
    p.PAT_SEX                     AS pat_sex
FROM PAT_ENC e
JOIN PAT_ENC_HSP h
    ON h.PAT_ENC_CSN_ID = e.PAT_ENC_CSN_ID
JOIN PATIENT p
    ON p.PAT_ID = e.PAT_ID
LEFT JOIN ZC_DISCH_DISP zd
    ON zd.DISCH_DISP_C = h.DISCH_DISP_C
WHERE h.HOSP_ADMSN_TIME >= DATEADD(year, -1, GETDATE())
    AND h.HOSP_DISCHRG_TIME IS NOT NULL
ORDER BY h.HOSP_ADMSN_TIME DESC;

SELECT
    op.ORDER_PROC_ID          AS order_id,
    op.PAT_ENC_CSN_ID         AS enctr_id,
    op.ORDER_TIME             AS order_ts,
    op.RESULT_TIME            AS result_ts,
    eap.PROC_NAME             AS proc_nm,
    eap.PROC_CODE             AS cpt_cd,
    r.LINE                    AS result_line,
    r.COMPONENT_ID            AS loinc_component_id,
    r.ORD_VALUE               AS result_val,
    r.REFERENCE_UNIT          AS result_unit,
    r.REFERENCE_LOW           AS ref_rng_low,
    r.REFERENCE_HIGH          AS ref_rng_high,
    r.RESULT_FLAG             AS abnrml_flg,
    r.RESULT_STATUS_C         AS result_sts_c
FROM ORDER_PROC op
JOIN CLARITY_EAP eap
    ON eap.PROC_ID = op.PROC_ID
LEFT JOIN ORDER_RESULTS r
    ON r.ORDER_ID = op.ORDER_PROC_ID
WHERE op.ORDER_TIME >= DATEADD(month, -3, GETDATE())
    AND op.ORDER_STATUS_C = 5   -- 5 = Resulted
ORDER BY op.ORDER_TIME DESC, r.LINE;

SELECT
    a.PAT_ENC_CSN_ID          AS enctr_id,
    a.EVENT_ID                AS adt_event_id,
    a.EVENT_TIME              AS adt_event_ts,
    za.NAME                   AS adt_event_type,
    a.FROM_BASE_CLASS_C       AS from_pat_cls_c,
    a.TO_BASE_CLASS_C         AS to_pat_cls_c,
    fd.DEPARTMENT_NAME        AS from_dept_nm,
    td.DEPARTMENT_NAME        AS to_dept_nm,
    a.FROM_ROOM_ID            AS from_room_id,
    a.TO_ROOM_ID              AS to_room_id,
    DATEDIFF(minute,
        LAG(a.EVENT_TIME) OVER
            (PARTITION BY a.PAT_ENC_CSN_ID
             ORDER BY a.EVENT_TIME),
        a.EVENT_TIME)         AS mins_in_prior_location
FROM CLARITY_ADT a
LEFT JOIN ZC_ADT_EVENT_TYPE za
    ON za.EVENT_TYPE_C = a.EVENT_TYPE_C
LEFT JOIN CLARITY_DEP fd
    ON fd.DEPARTMENT_ID = a.FROM_DEPARTMENT_ID
LEFT JOIN CLARITY_DEP td
    ON td.DEPARTMENT_ID = a.TO_DEPARTMENT_ID
WHERE a.EVENT_TIME >= DATEADD(day, -30, GETDATE())
ORDER BY a.PAT_ENC_CSN_ID, a.EVENT_TIME;