Diagnoses and Problems: Two Sides of the Same Coin

Purpose: To explain the critical distinction between episodic diagnoses (what was treated during a visit) and longitudinal problems (a patient’s ongoing health issues), and to teach you how to query both correctly.

Two Views of Patient Conditions

Epic separates patient conditions into two fundamentally different but related concepts:

1. Encounter Diagnoses: Conditions addressed during a specific visit. These are temporary and visit-specific.
2. Problem List: A curated list of ongoing health issues, like diabetes or hypertension, that are tracked longitudinally across all encounters.

Understanding this distinction is crucial for accurate clinical analysis, quality reporting, and population health management.

Compare the record counts of the two diagnosis storage models

View SQL Query

-- Encounter diagnoses: Multiple per visit
SELECT 'Encounter Diagnoses' as type, COUNT(*) as total_records
FROM PAT_ENC_DX

UNION ALL

-- Problem list: Ongoing conditions
SELECT 'Problem List', COUNT(*)
FROM PROBLEM_LIST;

type	total_records
Encounter Diagnoses	32
Problem List	2

Encounter Diagnoses: The PAT_ENC_DX Table

Every encounter can have multiple diagnoses, organized using the familiar (ID, LINE) pattern. These represent the clinician’s assessment for that specific visit.

Examine the diagnosis structure for a single encounter

View SQL Query

SELECT 
    e.PAT_ENC_CSN_ID,
    e.LINE,
    e.DX_ID,
    e.PRIMARY_DX_YN,
    -- Join to get the diagnosis name
    d.DX_NAME
FROM PAT_ENC_DX e
JOIN CLARITY_EDG d ON e.DX_ID = d.DX_ID
WHERE e.PAT_ENC_CSN_ID = 720803470
ORDER BY e.LINE;

PAT_ENC_CSN_ID	LINE	DX_ID	PRIMARY_DX_YN	DX_NAME
720803470	1	444804	Y	Encounter to establish care
720803470	2	1181154	N	Gastroesophageal reflux disease, esophagitis presence not specified
720803470	3	94362	N	Dyspepsia

Crucially, the LINE number indicates entry order, NOT clinical priority.

The Primary Diagnosis Flag

To identify the main reason for the visit, Epic uses an explicit flag, PRIMARY_DX_YN.

Prove that LINE number does not indicate priority

View SQL Query

-- Find cases where the primary diagnosis is NOT at LINE 1
SELECT 
    PAT_ENC_CSN_ID,
    LINE,
    PRIMARY_DX_YN,
    DX_ID,
    CASE 
        WHEN PRIMARY_DX_YN = 'Y' AND LINE > 1 
        THEN 'Primary diagnosis at LINE ' || LINE || '!'
        ELSE 'Normal'
    END as note
FROM PAT_ENC_DX
WHERE PRIMARY_DX_YN = 'Y' AND LINE > 1
LIMIT 5;

PAT_ENC_CSN_ID	LINE	PRIMARY_DX_YN	DX_ID	note
991225117	3	Y	192858	Primary diagnosis at LINE 3!
948004323	2	Y	192858	Primary diagnosis at LINE 2!

Always use PRIMARY_DX_YN = 'Y' to identify the primary diagnosis; never assume LINE = 1.

The Problem List: A Longitudinal View

The PROBLEM_LIST table tracks a patient’s ongoing health conditions over time. This list is actively managed by the care team.

View a patient's active problem list

View SQL Query

SELECT 
    pl.PROBLEM_LIST_ID,
    pl.DESCRIPTION,
    pl.PROBLEM_STATUS_C_NAME,
    pl.DATE_OF_ENTRY,
    pl.RESOLVED_DATE,
    pl.CHRONIC_YN
FROM PAT_PROBLEM_LIST ppl
JOIN PROBLEM_LIST pl ON ppl.PROBLEM_LIST_ID = pl.PROBLEM_LIST_ID
WHERE ppl.PAT_ID = 'Z7004242' AND pl.PROBLEM_STATUS_C_NAME = 'Active'
ORDER BY pl.DATE_OF_ENTRY;

PROBLEM_LIST_ID	DESCRIPTION	PROBLEM_STATUS_C_NAME	DATE_OF_ENTRY	RESOLVED_DATE	CHRONIC_YN
90574164		Active	8/29/2022 12:00:00 AM		N
30694847		Active	8/9/2018 12:00:00 AM		N

Problems have three key statuses:

• Active: A current health issue.
• Resolved: The issue is no longer a concern.
• Deleted: The problem was entered in error.

Linking Encounter Diagnoses to the Problem List

An encounter diagnosis can be linked to an existing problem or used to create a new one. The DX_LINK_PROB_ID field in PAT_ENC_DX creates this connection.

Find when ongoing problems were addressed in specific encounters

View SQL Query

SELECT 
    SUBSTR(pe.CONTACT_DATE, 1, 10) as Visit_Date,
    pl.DESCRIPTION as Problem_Addressed
FROM PAT_ENC pe
JOIN PAT_ENC_DX ped ON pe.PAT_ENC_CSN_ID = ped.PAT_ENC_CSN_ID
JOIN PROBLEM_LIST pl ON ped.DX_LINK_PROB_ID = pl.PROBLEM_LIST_ID
WHERE pe.PAT_ID = 'Z7004242'
  AND ped.DX_LINK_PROB_ID IS NOT NULL
ORDER BY pe.CONTACT_DATE DESC;

Visit_Date	Problem_Addressed
9/28/2023
8/9/2018 1
8/29/2022
8/18/2018
3/2/2023 1
2/19/2023
12/22/2023
12/22/2022
12/1/2022
1/9/2020 1

The Diagnosis Master: CLARITY_EDG

Both systems link to the CLARITY_EDG table, which contains the master list of all diagnosis codes and their descriptions.

Look up common diagnoses in the master table

View SQL Query

SELECT 
    DX_ID,
    DX_NAME
FROM CLARITY_EDG
WHERE DX_NAME LIKE '%reflux%' OR DX_NAME LIKE '%diabetes%'
ORDER BY DX_ID;

DX_ID	DX_NAME
15362	Screening for diabetes mellitus
70859	Gastroesophageal reflux disease
513611	Encounter for screening for diabetes mellitus
513919	Gastro-esophageal reflux disease without esophagitis
1181154	Gastroesophageal reflux disease, esophagitis presence not specified

Note that this EHI extract contains Epic’s internal diagnosis identifiers (DX_ID), not standardized ICD codes.

Analyzing a Patient’s Complete Diagnostic History

By combining these tables, you can create a comprehensive view of a patient’s health conditions.

Create a timeline of all diagnoses for a patient

View SQL Query

SELECT 
    p.CONTACT_DATE,
    e.LINE,
    e.PRIMARY_DX_YN,
    d.DX_NAME,
    e.DX_CHRONIC_YN,
    CASE WHEN e.DX_LINK_PROB_ID IS NOT NULL THEN 'Yes' ELSE 'No' END as Linked_to_Problem_List
FROM PAT_ENC_DX e
JOIN CLARITY_EDG d ON e.DX_ID = d.DX_ID
JOIN PAT_ENC p ON e.PAT_ENC_CSN_ID = p.PAT_ENC_CSN_ID
WHERE p.PAT_ID = 'Z7004242'
ORDER BY p.PAT_ENC_DATE_REAL, e.LINE;

CONTACT_DATE	LINE	PRIMARY_DX_YN	DX_NAME	DX_CHRONIC_YN	Linked_to_Problem_List
8/9/2018 12:00:00 AM	1	Y	Encounter to establish care	N	No
8/9/2018 12:00:00 AM	2	N	Gastroesophageal reflux disease, esophagitis presence not specified	N	Yes
8/9/2018 12:00:00 AM	3	N	Dyspepsia	N	No
8/9/2018 12:00:00 AM	1	N	Encounter to establish care	N	No
8/18/2018 12:00:00 AM	1	N	Gastroesophageal reflux disease, esophagitis presence not specified	N	Yes
8/18/2018 12:00:00 AM	2	N	Dyspepsia	N	No
1/9/2020 12:00:00 AM	1	Y	Gastroesophageal reflux disease, esophagitis presence not specified	N	Yes
1/9/2020 12:00:00 AM	2	N	Lipoma of head	N	No
1/9/2020 12:00:00 AM	3	N	Nevus	N	No
7/14/2020 12:00:00 AM	1	Y	Traumatic injury of head, initial encounter	N	No
7/15/2020 12:00:00 AM	1	Y	Traumatic injury of head, initial encounter	N	No
7/15/2020 12:00:00 AM	2	N	Post-concussion headache	N	No
7/16/2020 12:00:00 AM	1	Y	Traumatic injury of head, initial encounter	N	No
7/21/2020 12:00:00 AM	1	Y	Traumatic injury of head, initial encounter	N	No
7/31/2020 12:00:00 AM	1	N	Traumatic injury of head, initial encounter	N	No
2/9/2022 12:00:00 AM	1	Y	Late effect of traumatic injury to brain	N	No
8/29/2022 12:00:00 AM	1	N	Primary hypertension	N	No
8/29/2022 12:00:00 AM	2	Y	Preventative health care	N	No
8/29/2022 12:00:00 AM	3	N	Post concussion syndrome	N	Yes
8/29/2022 12:00:00 AM	1	N	Primary hypertension	N	No
12/1/2022 12:00:00 AM	1	Y	Post concussion syndrome	N	Yes
12/22/2022 12:00:00 AM	1	Y	Post concussion syndrome	N	Yes
2/19/2023 12:00:00 AM	1	N	Post concussion syndrome	N	Yes
3/2/2023 12:00:00 AM	1	Y	Post concussion syndrome	N	Yes
9/28/2023 12:00:00 AM	1	N	Screening for hyperlipidemia	N	No
9/28/2023 12:00:00 AM	2	N	Screening for diabetes mellitus	N	No
9/28/2023 12:00:00 AM	3	Y	Preventative health care	N	No
9/28/2023 12:00:00 AM	4	N	Post concussion syndrome	N	Yes
9/28/2023 12:00:00 AM	1	N	Screening for diabetes mellitus	N	No
9/28/2023 12:00:00 AM	2	N	Screening for hyperlipidemia	N	No
9/28/2023 12:00:00 AM	3	N	Preventative health care	N	No
12/22/2023 12:00:00 AM	1	N	Post concussion syndrome	N	Yes

---

Key Takeaways

• Two Systems: Epic uses two systems to track conditions: PAT_ENC_DX for visit-specific diagnoses and PROBLEM_LIST for ongoing issues.
• Primary Diagnosis: Use PRIMARY_DX_YN = 'Y' to find the main diagnosis for a visit; do not rely on LINE = 1.
• Longitudinal View: The PROBLEM_LIST provides a patient’s long-term health summary.
• Linking: The DX_LINK_PROB_ID field connects a visit diagnosis to an ongoing problem.
• Master List: CLARITY_EDG is the dictionary for all diagnosis codes (DX_ID).
• No ICD Codes: This EHI export uses internal DX_IDs, not standardized ICD codes, for clinical diagnoses. ICD codes are found separately in billing data.

---