Your Patient Journey Data Template

[REPORT CONFIGURATION SPECIFICATION]

# GENERAL SETTINGS
Application: Epic Reporting Workbench
Template_ID: Encounter_Search_LBF
Time_Horizon: Discharge Date between [Start Date] and [End Date]
Filters: Encounter Type IN ('Hospital Encounter', 'Emergency')

# COLUMN SELECTION MAPPING
# Map the following Epic RWB Columns (Display Names) to the Output Activities.
# Note: Column names may vary slightly by Epic customized build.

[MANDATORY ATTRIBUTES]
Column: Contact Serial Number (CSN)  -> Target: PatientEpisodeId
Column: Patient MRN                  -> Target: PatientMrn
Column: Department at Discharge      -> Target: DepartmentName
Column: Discharge Disposition        -> Target: DischargeDisposition
Column: Primary Diagnosis ICD-10     -> Target: PrimaryDiagnosisCode
Column: Attending Provider           -> Target: ProviderId
Column: Current Date                 -> Target: LastDataUpdate
Column: System Name (Fixed 'Epic')   -> Target: SourceSystem

[ACTIVITY TIMESTAMP MAPPING]
# These columns represent the 'EventTimestamp' for the specific 'ActivityName'

1.  Activity: Patient Registered
    Epic_Column: Hospital Admission Time OR Check-In Time

2.  Activity: Triage Completed
    Epic_Column: Triage End Time OR Triage Acuity Time

3.  Activity: Care Plan Initiated
    Epic_Column: Care Plan Start Date

4.  Activity: Diagnostic Test Ordered
    Epic_Column: First Lab Order Time OR First Imaging Order Time
    (Note: Select 'Earliest' if multiple columns exist)

5.  Activity: Diagnostic Test Performed
    Epic_Column: First Lab Result Time OR First Imaging End Time

6.  Activity: Diagnosis Confirmed
    Epic_Column: Principal Diagnosis Problem List Date

7.  Activity: Consultation Requested
    Epic_Column: Consult Order Create Time

8.  Activity: Consultation Completed
    Epic_Column: Consult Complete Time

9.  Activity: Medication Administered
    Epic_Column: First Medication Administration Time

10. Activity: Transfer Ordered
    Epic_Column: Transfer Order Time

11. Activity: Patient Transferred
    Epic_Column: Last Transfer In Time OR ADT Event Time

12. Activity: Discharge Planning Initiated
    Epic_Column: Case Management Start Date

13. Activity: Discharge Order Signed
    Epic_Column: Discharge Order Time

14. Activity: Patient Discharged
    Epic_Column: Hospital Discharge Time

15. Activity: Follow-up Appointment Scheduled
    Epic_Column: Discharge Follow-Up Appointment Made Date

# TRANSFORMATION LOGIC (PSEUDO-CODE)
# The export will be 'Wide'. Apply this logic to create the Event Log:

FOR EACH Row IN Exported_CSV:
  EpisodeID = Row['Contact Serial Number']
  
  FUNCTION CreateEvent(ActivityName, TimestampColumn):
    IF Row[TimestampColumn] IS NOT NULL:
       OUTPUT_ROW = {
         'PatientEpisodeId': EpisodeID,
         'ActivityName': ActivityName,
         'EventTimestamp': Row[TimestampColumn],
         'PatientMrn': Row['Patient MRN'],
         'DepartmentName': Row['Department at Discharge'],
         ... [All Attributes]
       }
       APPEND OUTPUT_ROW TO Event_Log

  # Execute for all 15 mappings defined above
  CreateEvent('Patient Registered', 'Hospital Admission Time')
  CreateEvent('Triage Completed', 'Triage End Time')
  ... [Repeat for all mapped columns]

END LOOP

WITH Cohort AS (
    SELECT 
        pe.PAT_ENC_CSN_ID,
        pe.PAT_ID,
        pe.HOSP_ADMSN_TIME,
        pe.HOSP_DISCH_TIME,
        pe.DEPARTMENT_ID,
        dep.DEPARTMENT_NAME,
        emp.NAME AS ProviderName,
        pat.PAT_MRN_ID,
        pe.ACUITY_LEVEL_C,
        disch.NAME AS DischargeDisposition,
        pe.ENC_TYPE_C
    FROM PAT_ENC pe
    LEFT JOIN CLARITY_DEP dep ON pe.DEPARTMENT_ID = dep.DEPARTMENT_ID
    LEFT JOIN CLARITY_EMP emp ON pe.VISIT_PROV_ID = emp.PROV_ID
    LEFT JOIN PATIENT pat ON pe.PAT_ID = pat.PAT_ID
    LEFT JOIN ZC_DISCH_DISP disch ON pe.DISCH_DISP_C = disch.DISCH_DISP_C
    WHERE pe.HOSP_ADMSN_TIME >= DATEADD(month, -6, GETDATE())
      AND pe.ENC_TYPE_C IN (3, 50) -- 3=Inpatient, 50=Emergency (Verify local codes)
)

-- 1. Patient Registered
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)) AS PatientEpisodeId,
    'Patient Registered' AS ActivityName,
    c.HOSP_ADMSN_TIME AS EventTimestamp,
    c.DepartmentName,
    c.ProviderName AS ProviderId,
    c.PAT_MRN_ID AS PatientMrn,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)) AS TriageAcuityLevel,
    CAST(c.ENC_TYPE_C AS VARCHAR(50)) AS EncounterType,
    NULL AS PrimaryDiagnosisCode,
    NULL AS ReadmissionFlag,
    c.DischargeDisposition,
    'Epic EHR' AS SourceSystem,
    GETDATE() AS LastDataUpdate
FROM Cohort c
WHERE c.HOSP_ADMSN_TIME IS NOT NULL

UNION ALL

-- 2. Triage Completed (Using Flowsheet or Triage Time)
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Triage Completed',
    ISNULL(pe.TRIAGE_END_INSTANT, c.HOSP_ADMSN_TIME), -- Fallback if specific column unused
    c.DepartmentName,
    c.ProviderName,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN PAT_ENC pe ON c.PAT_ENC_CSN_ID = pe.PAT_ENC_CSN_ID
WHERE pe.TRIAGE_END_INSTANT IS NOT NULL

UNION ALL

-- 3. Care Plan Initiated (Based on Order Type)
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Care Plan Initiated',
    ord.ORDER_INST,
    c.DepartmentName,
    emp.NAME,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN ORDER_PROC ord ON c.PAT_ENC_CSN_ID = ord.PAT_ENC_CSN_ID
LEFT JOIN CLARITY_EMP emp ON ord.ORDERING_PROV_ID = emp.PROV_ID
WHERE ord.ORDER_TYPE_C = 100 -- Placeholder: Replace with ID for Care Plan/Protocol

UNION ALL

-- 4. Diagnostic Test Ordered (Lab/Radiology)
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Diagnostic Test Ordered',
    ord.ORDER_INST,
    c.DepartmentName,
    emp.NAME,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN ORDER_PROC ord ON c.PAT_ENC_CSN_ID = ord.PAT_ENC_CSN_ID
LEFT JOIN CLARITY_EMP emp ON ord.ORDERING_PROV_ID = emp.PROV_ID
WHERE ord.ORDER_TYPE_C IN (1, 2) -- Placeholder: 1=Lab, 2=Radiology (Verify local codes)

UNION ALL

-- 5. Diagnostic Test Performed (Result Time or Procedure Start)
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Diagnostic Test Performed',
    COALESCE(ord2.PROC_START_TIME, ord2.PROC_ENDING_TIME, ord.ORDER_INST),
    c.DepartmentName,
    emp.NAME,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN ORDER_PROC ord ON c.PAT_ENC_CSN_ID = ord.PAT_ENC_CSN_ID
JOIN ORDER_PROC_2 ord2 ON ord.ORDER_PROC_ID = ord2.ORDER_PROC_ID
LEFT JOIN CLARITY_EMP emp ON ord.ORDERING_PROV_ID = emp.PROV_ID
WHERE ord.ORDER_TYPE_C IN (1, 2)
  AND ord2.PROC_START_TIME IS NOT NULL

UNION ALL

-- 6. Diagnosis Confirmed
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Diagnosis Confirmed',
    dx.NOTED_DATE,
    c.DepartmentName,
    c.ProviderName,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    edg.DX_NAME,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN PAT_ENC_DX dx ON c.PAT_ENC_CSN_ID = dx.PAT_ENC_CSN_ID
JOIN CLARITY_EDG edg ON dx.DX_ID = edg.DX_ID
WHERE dx.NOTED_DATE IS NOT NULL

UNION ALL

-- 7. Consultation Requested
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Consultation Requested',
    ord.ORDER_INST,
    c.DepartmentName,
    emp.NAME,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN ORDER_PROC ord ON c.PAT_ENC_CSN_ID = ord.PAT_ENC_CSN_ID
LEFT JOIN CLARITY_EMP emp ON ord.ORDERING_PROV_ID = emp.PROV_ID
WHERE ord.ORDER_TYPE_C = 35 -- Placeholder: Replace with ID for Consult

UNION ALL

-- 8. Consultation Completed
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Consultation Completed',
    ord.ORDER_END_TIME,
    c.DepartmentName,
    emp.NAME,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN ORDER_PROC ord ON c.PAT_ENC_CSN_ID = ord.PAT_ENC_CSN_ID
LEFT JOIN CLARITY_EMP emp ON ord.ORDERING_PROV_ID = emp.PROV_ID
WHERE ord.ORDER_TYPE_C = 35 -- Placeholder: Replace with ID for Consult
  AND ord.ORDER_STATUS_C = 5 -- Placeholder: 5=Completed
  AND ord.ORDER_END_TIME IS NOT NULL

UNION ALL

-- 9. Medication Administered
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Medication Administered',
    mar.TAKEN_TIME,
    c.DepartmentName,
    emp.NAME,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN ORDER_MED med ON c.PAT_ENC_CSN_ID = med.PAT_ENC_CSN_ID
JOIN MAR_ADMIN_INFO mar ON med.ORDER_MED_ID = mar.ORDER_MED_ID
LEFT JOIN CLARITY_EMP emp ON mar.TAKEN_USER_ID = emp.USER_ID
WHERE mar.TAKEN_TIME IS NOT NULL
  AND mar.MAR_ACTION_C = 1 -- Placeholder: 1=Given

UNION ALL

-- 10. Transfer Ordered
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Transfer Ordered',
    ord.ORDER_INST,
    c.DepartmentName,
    emp.NAME,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN ORDER_PROC ord ON c.PAT_ENC_CSN_ID = ord.PAT_ENC_CSN_ID
LEFT JOIN CLARITY_EMP emp ON ord.ORDERING_PROV_ID = emp.PROV_ID
WHERE ord.ORDER_TYPE_C = 60 -- Placeholder: Replace with ID for Transfer/Bed Request

UNION ALL

-- 11. Patient Transferred
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Patient Transferred',
    adt.EFFECTIVE_TIME,
    dep.DEPARTMENT_NAME,
    c.ProviderName,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN CLARITY_ADT adt ON c.PAT_ENC_CSN_ID = adt.PAT_ENC_CSN_ID
LEFT JOIN CLARITY_DEP dep ON adt.DEPARTMENT_ID = dep.DEPARTMENT_ID
WHERE adt.EVENT_TYPE_C = 3 -- 3=Transfer In

UNION ALL

-- 12. Discharge Planning Initiated
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Discharge Planning Initiated',
    ord.ORDER_INST,
    c.DepartmentName,
    emp.NAME,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN ORDER_PROC ord ON c.PAT_ENC_CSN_ID = ord.PAT_ENC_CSN_ID
LEFT JOIN CLARITY_EMP emp ON ord.ORDERING_PROV_ID = emp.PROV_ID
WHERE ord.ORDER_TYPE_C = 70 -- Placeholder: Case Management/Discharge Order Type

UNION ALL

-- 13. Discharge Order Signed
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Discharge Order Signed',
    ord.ORDER_INST,
    c.DepartmentName,
    emp.NAME,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN ORDER_PROC ord ON c.PAT_ENC_CSN_ID = ord.PAT_ENC_CSN_ID
LEFT JOIN CLARITY_EMP emp ON ord.ORDERING_PROV_ID = emp.PROV_ID
WHERE ord.PROC_CODE = 'DISCHARGE' -- Placeholder: Filter by specific discharge procedure code

UNION ALL

-- 14. Patient Discharged
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Patient Discharged',
    c.HOSP_DISCH_TIME,
    c.DepartmentName,
    c.ProviderName,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
WHERE c.HOSP_DISCH_TIME IS NOT NULL

UNION ALL

-- 15. Follow-up Appointment Scheduled
SELECT
    CAST(c.PAT_ENC_CSN_ID AS VARCHAR(50)),
    'Follow-up Appointment Scheduled',
    next_pe.APPT_MADE_DATE,
    c.DepartmentName,
    c.ProviderName,
    c.PAT_MRN_ID,
    CAST(c.ACUITY_LEVEL_C AS VARCHAR(50)),
    CAST(c.ENC_TYPE_C AS VARCHAR(50)),
    NULL,
    NULL,
    c.DischargeDisposition,
    'Epic EHR',
    GETDATE()
FROM Cohort c
JOIN PAT_ENC next_pe ON c.PAT_ID = next_pe.PAT_ID
WHERE next_pe.APPT_MADE_DATE BETWEEN c.HOSP_ADMSN_TIME AND ISNULL(c.HOSP_DISCH_TIME, GETDATE())
  AND next_pe.CONTACT_DATE > c.HOSP_ADMSN_TIME -- The appointment is for a future date relative to admission