Encounters and Patient Movement

Purpose: To dissect the encounter—the central organizing event in healthcare—and understand how Epic tracks every patient interaction and movement through the system, from outpatient clinics to hospital stays.

The Spine of Clinical Data

Every diagnosis, order, and clinical note connects to an encounter. It’s the fundamental organizing principle of the medical record, representing each discrete interaction between a patient and the healthcare system. Epic’s encounter model captures everything from a five-minute telehealth check-in to a month-long hospital stay.

View recent patient encounters

View SQL Query

SELECT 
    pe.PAT_ENC_CSN_ID as CSN,
    SUBSTR(pe.CONTACT_DATE, 1, 10) as Visit_Date,
    cd.DEPARTMENT_NAME as Department,
    cs.PROV_NAME as Provider,
    pe.APPT_STATUS_C_NAME as Status
FROM PAT_ENC pe
LEFT JOIN CLARITY_DEP cd ON pe.DEPARTMENT_ID = cd.DEPARTMENT_ID
LEFT JOIN CLARITY_SER cs ON pe.VISIT_PROV_ID = cs.PROV_ID
WHERE pe.PAT_ID = 'Z7004242'
ORDER BY pe.CONTACT_DATE DESC
LIMIT 10;

CSN	Visit_Date	Department	Provider	Status
837844366	9/8/2020 1	MAC APL INTERNAL MEDICINE	LOUGH, KAREN L
991225117	9/28/2023	MAC APL INTERNAL MEDICINE	RAMMELKAMP, ZOE L	Completed
1028739468	9/28/2023	MAC APL INTERNAL MEDICINE	RAMMELKAMP, ZOE L
1028743701	9/28/2023	MAC APL LABORATORY	MAC LAB APL	Completed
1028766353	9/28/2023	MAC APL INTERNAL MEDICINE	RAMMELKAMP, ZOE L
787137346	9/28/2019	NULL	NULL
1028606559	9/27/2023	NULL	NULL
1027863229	9/25/2023	NULL	NULL
962603110	9/25/2022	NULL	NULL
898794674	9/25/2021	NULL	NULL

Each row represents a unique patient interaction, identified by the Contact Serial Number (CSN).

Understanding the CSN (Contact Serial Number)

The PAT_ENC_CSN_ID is Epic’s unique identifier for every patient interaction. It serves as the universal foreign key linking all encounter-related data.

Verify CSN uniqueness and its role as a primary key

View SQL Query

SELECT 
    COUNT(*) as total_encounters,
    COUNT(DISTINCT PAT_ENC_CSN_ID) as unique_csns,
    CASE 
        WHEN COUNT(*) = COUNT(DISTINCT PAT_ENC_CSN_ID) 
        THEN 'CSNs are unique (primary key)' 
        ELSE 'CSNs are not unique' 
    END as verification
FROM PAT_ENC;

total_encounters	unique_csns	verification
111	111	CSNs are unique (primary key)

The Magic of PAT_ENC_DATE_REAL

When multiple encounters occur on the same day, how do you maintain chronological order? Epic’s elegant solution uses decimal sequencing.

Demonstrate the decimal date pattern for chronological sorting

View SQL Query

SELECT 
    PAT_ID,
    CONTACT_DATE,
    PAT_ENC_DATE_REAL,
    -- Extract the sequence number
    ROUND((PAT_ENC_DATE_REAL - CAST(PAT_ENC_DATE_REAL AS INT)) * 100, 0) + 1 as encounter_sequence
FROM PAT_ENC
WHERE CONTACT_DATE = '9/28/2023 12:00:00 AM'
ORDER BY PAT_ENC_DATE_REAL;

PAT_ID	CONTACT_DATE	PAT_ENC_DATE_REAL	encounter_sequence
Z7004242	9/28/2023 12:00:00 AM	66745	1
Z7004242	9/28/2023 12:00:00 AM	66745.01	2
Z7004242	9/28/2023 12:00:00 AM	66745.02	3
Z7004242	9/28/2023 12:00:00 AM	66745.03	4

The decimal portion (0.00, 0.01, 0.02…) provides unique sequencing for same-day encounters, guaranteeing a correct timeline.

Encounter Types and Settings

Encounters occur in various settings, which can often be identified by the department name or specific encounter type fields.

Analyze encounter types across different departments

View SQL Query

SELECT 
    cd.DEPARTMENT_NAME as Department,
    COUNT(*) as Count,
    MIN(SUBSTR(pe.CONTACT_DATE, 1, 10)) as First_Visit,
    MAX(SUBSTR(pe.CONTACT_DATE, 1, 10)) as Last_Visit
FROM PAT_ENC pe
LEFT JOIN CLARITY_DEP cd ON pe.DEPARTMENT_ID = cd.DEPARTMENT_ID
WHERE pe.PAT_ID = 'Z7004242'
GROUP BY cd.DEPARTMENT_NAME
ORDER BY Count DESC;

Department	Count	First_Visit	Last_Visit
NULL	66	1/25/2019	9/28/2019
MAC APL INTERNAL MEDICINE	33	1/9/2020 1	9/8/2020 1
MAC APL LABORATORY	5	10/11/2022	9/28/2023
MHM OT NEURO CENTRAL	2	3/11/2022	3/22/2022
GENERIC EXTERNAL DATA DEPARTMENT	2	3/11/2022	9/21/2020
MHM CENTRAL SCHEDULING	1	2/9/2022 1	2/9/2022 1
MCM W WASH PEDS	1	2/1/2019 1	2/1/2019 1
MAC APL BUSINESS SERVICES	1	8/9/2018 1	8/9/2018 1

Hospital Admissions

For inpatient stays, PAT_ENC and the related PAT_ENC_HSP table track admission and discharge details.

View hospital admissions and length of stay

View SQL Query

SELECT 
    pe.PAT_ENC_CSN_ID as CSN,
    SUBSTR(pe.HOSP_ADMSN_TIME, 1, 10) as Admission_Date,
    SUBSTR(pe.HOSP_DISCHRG_TIME, 1, 10) as Discharge_Date,
    pe.HOSP_ADMSN_TYPE_C_NAME as Admission_Type,
    hsp.DISCH_DISP_C_NAME as Discharge_Disposition,
    CASE 
        WHEN pe.HOSP_ADMSN_TIME IS NOT NULL AND pe.HOSP_DISCHRG_TIME IS NOT NULL
        THEN ROUND(julianday(pe.HOSP_DISCHRG_TIME) - julianday(pe.HOSP_ADMSN_TIME), 2)
        ELSE NULL
    END as los_days
FROM PAT_ENC pe
LEFT JOIN PAT_ENC_HSP hsp ON pe.PAT_ENC_CSN_ID = hsp.PAT_ENC_CSN_ID
WHERE pe.PAT_ID = 'Z7004242'
  AND pe.HOSP_ADMSN_TIME IS NOT NULL
ORDER BY pe.HOSP_ADMSN_TIME DESC;

CSN	Admission_Date	Discharge_Date	Admission_Type	Discharge_Disposition	los_days
922943112	3/22/2022	3/22/2022	Elective	Home - Discharge to Home or Self Care	NULL
922942674	3/11/2022	3/11/2022	Elective	Home - Discharge to Home or Self Care	NULL
720803470			Elective	NULL	NULL
724619887				NULL	NULL
724623985				NULL	NULL
724628999			Elective	NULL	NULL
725327197			Elective	NULL	NULL
727947624				NULL	NULL
736485409				NULL	NULL
740764099				NULL	NULL
745238411				NULL	NULL
749745972				NULL	NULL
750474644				NULL	NULL
754166145				NULL	NULL
758599939				NULL	NULL
763443168				NULL	NULL
768007053				NULL	NULL
772693619				NULL	NULL
777174829				NULL	NULL
781842097				NULL	NULL
787137346				NULL	NULL
791983409				NULL	NULL
797089043				NULL	NULL
799951565			Elective	NULL	NULL
802802103				NULL	NULL
827277205				NULL	NULL
829212157			Elective	NULL	NULL
829213099			Elective	NULL	NULL
829273579				NULL	NULL
829282937				NULL	NULL
829385742				NULL	NULL
829393933				NULL	NULL
829467718				NULL	NULL
829995922				NULL	NULL
829996120				NULL	NULL
830043848				NULL	NULL
830047706				NULL	NULL
831452662				NULL	NULL
832464108				NULL	NULL
833901305				NULL	NULL
836036457				NULL	NULL
837844366				NULL	NULL
839256727				NULL	NULL
840888730				NULL	NULL
845590414				NULL	NULL
850593484				NULL	NULL
854992547				NULL	NULL
859392791				NULL	NULL
864389217				NULL	NULL
869002878				NULL	NULL
873928397				NULL	NULL
878774242				NULL	NULL
883637721				NULL	NULL
888227767				NULL	NULL
893473416				NULL	NULL
898794674				NULL	NULL
921952141			Elective	NULL	NULL
922943525				NULL	NULL
924936761				NULL	NULL
927098738				NULL	NULL
927200229				NULL	NULL
928532643				NULL	NULL
930010268				NULL	NULL
935515793				NULL	NULL
940919045				NULL	NULL
946189306				NULL	NULL
948002801			Elective	NULL	NULL
948004323			Elective	NULL	NULL
951207510				NULL	NULL
957265560				NULL	NULL
957995289				NULL	NULL
958134730				NULL	NULL
958147754			Elective	NULL	NULL
958148226			Elective	NULL	NULL
958148810			Elective	NULL	NULL
962603110				NULL	NULL
968143108				NULL	NULL
973908013				NULL	NULL
974452422				NULL	NULL
974614965			Elective	NULL	NULL
974974125				NULL	NULL
977858467				NULL	NULL
979218048				NULL	NULL
984606712				NULL	NULL
988126821				NULL	NULL
990151874				NULL	NULL
991215738				NULL	NULL
991221485				NULL	NULL
991225117			Elective	NULL	NULL
995315871				NULL	NULL
996534320				NULL	NULL
1000825434				NULL	NULL
1006342664				NULL	NULL
1011533066				NULL	NULL
1016759309				NULL	NULL
1018439080				NULL	NULL
1020583168				NULL	NULL
1022460205				NULL	NULL
1027863229				NULL	NULL
1028606559				NULL	NULL

The ADT Event Trail: Tracking Patient Movement

The CLARITY_ADT table provides a detailed audit trail of patient movement (Admission, Discharge, Transfer) within a facility.

Show the complete patient journey for an encounter

View SQL Query

SELECT 
    EVENT_TYPE_C_NAME,
    EFFECTIVE_TIME,
    -- Calculate time between events
    ROUND((julianday(EFFECTIVE_TIME) - 
           julianday(LAG(EFFECTIVE_TIME) OVER (ORDER BY EFFECTIVE_TIME))) * 24, 2) as hours_since_last_event
FROM CLARITY_ADT
WHERE PAT_ENC_CSN_ID = 922942674
ORDER BY EFFECTIVE_TIME;

EVENT_TYPE_C_NAME	EFFECTIVE_TIME	hours_since_last_event
Discharge	3/11/2022 11:59:00 PM	NULL
Hospital Outpatient	3/11/2022 2:41:00 PM	NULL

Appointment Status Lifecycle

The APPT_STATUS_C_NAME field tracks the workflow of scheduled appointments.

Analyze appointment status distribution

View SQL Query

SELECT 
    APPT_STATUS_C_NAME,
    COUNT(*) as count,
    ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM PAT_ENC), 1) as percentage
FROM PAT_ENC
GROUP BY APPT_STATUS_C_NAME
ORDER BY count DESC;

APPT_STATUS_C_NAME	count	percentage
	94	84.7
Completed	13	11.7
Canceled	3	2.7
Scheduled	1	0.9

Note that many encounters (especially non-scheduled or inpatient) may have a NULL status.

Provider Roles in Encounters

Epic tracks multiple provider relationships for each encounter.

Understand the different provider roles

View SQL Query

SELECT 
    pe.PAT_ENC_CSN_ID,
    vp.PROV_NAME as Visit_Provider,
    pcp.PROV_NAME as PCP_on_Visit,
    pe.REFERRAL_SOURCE_ID_REFERRING_PROV_NAM as Referring_Provider
FROM PAT_ENC pe
LEFT JOIN CLARITY_SER vp ON pe.VISIT_PROV_ID = vp.PROV_ID
LEFT JOIN CLARITY_SER pcp ON pe.PCP_PROV_ID = pcp.PROV_ID
WHERE pe.PAT_ID = 'Z7004242' AND pe.APPT_STATUS_C_NAME = 'Completed'
LIMIT 5;

PAT_ENC_CSN_ID	Visit_Provider	PCP_on_Visit	Referring_Provider
720803470	DHILLON, PUNEET S	DHILLON, PUNEET S	DHILLON, PUNEET S
724628999	MAC LAB APL	DHILLON, PUNEET S	DHILLON, PUNEET S
725327197	MAC LAB APL	DHILLON, PUNEET S	DHILLON, PUNEET S
799951565	DHILLON, PUNEET S	DHILLON, PUNEET S	DHILLON, PUNEET S
829213099	PICONE, MARY A	DHILLON, PUNEET S

• Visit Provider: The provider who saw the patient.
• PCP: The patient’s primary care provider at the time of the visit.
• Referring Provider: The provider who sent the patient for the visit.

Building an Encounter Timeline

To understand a patient’s care journey, you can construct a chronological timeline of their visits.

Create a patient's encounter timeline

View SQL Query

WITH encounter_timeline AS (
    SELECT 
        pe.PAT_ENC_CSN_ID,
        pe.CONTACT_DATE,
        pe.PAT_ENC_DATE_REAL,
        pe.APPT_STATUS_C_NAME,
        cd.DEPARTMENT_NAME,
        cs.PROV_NAME as Visit_Provider,
        -- Calculate days since last encounter
        ROUND(pe.PAT_ENC_DATE_REAL - LAG(pe.PAT_ENC_DATE_REAL) OVER (
            PARTITION BY pe.PAT_ID 
            ORDER BY pe.PAT_ENC_DATE_REAL
        ), 0) as days_since_last_visit
    FROM PAT_ENC pe
    LEFT JOIN CLARITY_DEP cd ON pe.DEPARTMENT_ID = cd.DEPARTMENT_ID
    LEFT JOIN CLARITY_SER cs ON pe.VISIT_PROV_ID = cs.PROV_ID
    WHERE pe.PAT_ID = 'Z7004242'
)
SELECT * FROM encounter_timeline
ORDER BY PAT_ENC_DATE_REAL
LIMIT 10;

PAT_ENC_CSN_ID	CONTACT_DATE	PAT_ENC_DATE_REAL	APPT_STATUS_C_NAME	DEPARTMENT_NAME	Visit_Provider	days_since_last_visit
720803470	8/9/2018 12:00:00 AM	64869	Completed	MAC APL INTERNAL MEDICINE	DHILLON, PUNEET S	NULL
724619887	8/9/2018 12:00:00 AM	64869.01		MAC APL BUSINESS SERVICES	DHILLON, PUNEET S	0
724623985	8/9/2018 12:00:00 AM	64869.02		MAC APL INTERNAL MEDICINE	DHILLON, PUNEET S	0
724628999	8/9/2018 12:00:00 AM	64869.03	Completed	MAC APL LABORATORY	MAC LAB APL	0
725327197	8/18/2018 12:00:00 AM	64878	Completed	MAC APL LABORATORY	MAC LAB APL	9
727947624	8/28/2018 12:00:00 AM	64888		MAC APL INTERNAL MEDICINE	WILD, DAWN M	10
736485409	10/25/2018 12:00:00 AM	64946		NULL	NULL	58
740764099	11/25/2018 12:00:00 AM	64977		NULL	NULL	31
745238411	12/25/2018 12:00:00 AM	65007		NULL	NULL	30
749745972	1/25/2019 12:00:00 AM	65038		NULL	NULL	31

---

Key Takeaways

• PAT_ENC is the master encounter table, with PAT_ENC_CSN_ID as the unique identifier for every interaction.
• PAT_ENC_DATE_REAL uses decimal sequencing to ensure unique and reliable chronological ordering.
• Encounters span all care settings, from outpatient appointments to inpatient hospital stays and ED visits.
• CLARITY_ADT provides a granular audit trail of patient movement during an admission.
• Multiple provider roles (Visit, PCP, Referring) are tracked for each encounter.
• Encounter types can be inferred from department names and admission type fields since this extract lacks a dedicated encounter type column.

---