Other Common Patterns

Purpose: To complete your pattern recognition toolkit with additional conventions found throughout Epic’s database.

Beyond the Big Five

You’ve mastered the major patterns, but Epic’s database contains several more conventions that appear repeatedly. Understanding these completes your ability to navigate any table confidently.

Discover the most common column patterns

View SQL Query

WITH pattern_analysis AS (
    SELECT 
        CASE
            WHEN column_name LIKE '%_YN' THEN '_YN (Yes/No)'
            WHEN column_name LIKE '%_ID_%NAME' THEN '_ID/_NAME pairs'
            WHEN column_name LIKE '%_AMT' THEN '_AMT (Amounts)'
            WHEN column_name LIKE '%_DTTM' THEN '_DTTM (DateTime)'
            WHEN column_name LIKE '%_DT' THEN '_DT (Date)'
            WHEN column_name LIKE '%_NUM' THEN '_NUM (Number)'
            WHEN column_name LIKE '%_CSN' THEN '_CSN (Contact Serial)'
            ELSE 'Other'
        END as pattern_type,
        column_name
    FROM _metadata
    WHERE column_name IS NOT NULL
)
SELECT 
    pattern_type,
    COUNT(*) as occurrence_count
FROM pattern_analysis
WHERE pattern_type != 'Other'
GROUP BY pattern_type
ORDER BY occurrence_count DESC

pattern_type	occurrence_count
_YN (Yes/No)	618
_ID/_NAME pairs	432
_DT (Date)	255
_DTTM (DateTime)	246
_AMT (Amounts)	226
_NUM (Number)	178
_CSN (Contact Serial)	40

The _YN Boolean Pattern

Epic uses _YN suffix for all boolean fields, with only two valid values:

Understand the _YN boolean pattern

View SQL Query

-- Check valid values
SELECT DISTINCT 
    PRIMARY_DX_YN as yn_value,
    COUNT(*) as occurrences
FROM PAT_ENC_DX
GROUP BY PRIMARY_DX_YN
ORDER BY yn_value;

-- See variety of boolean fields
SELECT 
    column_name,
    REPLACE(column_name, '_YN', '') as what_it_tracks
FROM _metadata
WHERE column_name LIKE '%_YN'
  AND table_name = 'PATIENT'
ORDER BY column_name
LIMIT 10

yn_value	occurrences
N	20
Y	12

The rules are simple:

• ‘Y’ = Yes/True
• ‘N’ = No/False
• NULL = Unknown/Not specified

Never expect ‘T’/‘F’, ‘1’/‘0’, or ‘true’/‘false’—Epic exclusively uses Y/N.

The ID/NAME Pairing Pattern

Epic frequently pairs technical IDs with human-readable names:

Explore ID/NAME pairs

View SQL Query

-- Find examples of paired columns
SELECT 
    REPLACE(column_name, '_NAME', '') as base_column,
    GROUP_CONCAT(column_name, ' + ') as paired_columns
FROM _metadata
WHERE table_name = 'PROBLEM_LIST'
  AND (column_name LIKE '%USER_ID' OR column_name LIKE '%USER_ID_NAME')
GROUP BY REPLACE(column_name, '_NAME', '')
ORDER BY base_column

base_column	paired_columns
ENTRY_USER_ID	ENTRY_USER_ID + ENTRY_USER_ID_NAME
NO_STAGE_USER_ID	NO_STAGE_USER_ID + NO_STAGE_USER_ID_NAME

This pattern serves two purposes:

1. ID: For joining and referential integrity
2. NAME: For immediate human readability without joins

See ID/NAME pairs in action

View SQL Query

SELECT 
    PROBLEM_LIST_ID,
    ENTRY_USER_ID,
    ENTRY_USER_ID_NAME,
    DESCRIPTION
FROM PROBLEM_LIST
WHERE ENTRY_USER_ID IS NOT NULL
LIMIT 5;

PROBLEM_LIST_ID	ENTRY_USER_ID	ENTRY_USER_ID_NAME	DESCRIPTION
90574164	RAMMELZL	RAMMELKAMP, ZOE L
30694847	DHILLOPS	DHILLON, PUNEET S

The Continuation Table Pattern (_2, _3, _4)

Epic splits wide tables across multiple physical tables:

Discover continuation tables

View SQL Query

SELECT 
    SUBSTR(name, 1, LENGTH(name) - 2) as base_table,
    GROUP_CONCAT(name, ', ') as all_parts,
    COUNT(*) as table_count
FROM sqlite_master
WHERE type = 'table'
  AND (name LIKE '%\_2' ESCAPE '\' 
       OR name LIKE '%\_3' ESCAPE '\' 
       OR name LIKE '%\_4' ESCAPE '\')
GROUP BY base_table
HAVING table_count >= 1
ORDER BY table_count DESC
LIMIT 10;

base_table	all_parts	table_count
REFERRAL	REFERRAL_2, REFERRAL_3, REFERRAL_4	3
PAT_ENC	PAT_ENC_2, PAT_ENC_3, PAT_ENC_4	3
PATIENT	PATIENT_2, PATIENT_3, PATIENT_4	3
ORDER_PROC	ORDER_PROC_2, ORDER_PROC_3, ORDER_PROC_4	3
ORDER_MED	ORDER_MED_2, ORDER_MED_3, ORDER_MED_4	3
HSP_ACCOUNT	HSP_ACCOUNT_2, HSP_ACCOUNT_3, HSP_ACCOUNT_4	3
CLM_VALUES	CLM_VALUES_2, CLM_VALUES_3, CLM_VALUES_4	3
SVC_LN_INFO	SVC_LN_INFO_2, SVC_LN_INFO_3	2
ORDER_DISP_INFO	ORDER_DISP_INFO_2, ORDER_DISP_INFO_3	2
HSP_TRANSACTIONS	HSP_TRANSACTIONS_2, HSP_TRANSACTIONS_3	2

These aren’t separate entities—they’re chunks of the same logical record, split because:

• SQL Server has column limits
• Chronicles records can be enormous
• Historical reasons from older database versions

Always query them together:

SELECT p.*, p2.*, p3.*
FROM PATIENT p
LEFT JOIN PATIENT_2 p2 ON p.PAT_ID = p2.PAT_ID
LEFT JOIN PATIENT_3 p3 ON p.PAT_ID = p3.PAT_ID;

Date and Time Patterns

Epic uses multiple patterns for temporal data:

Analyze temporal column patterns

View SQL Query

WITH date_patterns AS (
    SELECT 
        CASE
            WHEN column_name LIKE '%_DTTM' THEN 'DateTime (_DTTM)'
            WHEN column_name LIKE '%_DT' AND column_name NOT LIKE '%_DTTM' THEN 'Date only (_DT)'
            WHEN column_name LIKE '%_DATE' THEN 'Date (spelled out)'
            WHEN column_name LIKE '%_TIME' THEN 'Time only (_TIME)'
            WHEN column_name LIKE '%_TM' AND column_name NOT LIKE '%_DTTM' THEN 'Time (_TM)'
        END as pattern,
        column_name,
        table_name
    FROM _metadata
    WHERE column_name LIKE '%_DT%' 
       OR column_name LIKE '%_DATE%'
       OR column_name LIKE '%_TIME%'
       OR column_name LIKE '%_TM'
)
SELECT 
    pattern,
    COUNT(*) as column_count,
    SUBSTR(GROUP_CONCAT(DISTINCT column_name), 1, 100) as examples
FROM date_patterns
WHERE pattern IS NOT NULL
GROUP BY pattern
ORDER BY column_count DESC
LIMIT 5

pattern	column_count	examples
Date (spelled out)	445	CONTACT_DATE,BIRTHDATE,CONTRACT_EXP_DATE,HB_INS_AGING_DATE,HB_LAST_DEMAND_DATE,HB_LAST_STMT_DATE,HB_
Date only (_DT)	255	EB_LAST_D_INFO_DT,EB_LAST_D_STMT_DT,EB_LAST_INFO_ST_DT,EB_LAST_ND_STMT_DT,HB_BILL_NOTE_EXP_DT,HB_LAS
DateTime (_DTTM)	246	ABN_FOLLOW_UP_INST_DTTM,ADDR_CHG_INSTANT_DTTM,HBMYC_LST_DB_V_DTTM,HBMYC_LST_LT_V_DTTM,HBMYC_LST_ST_V
Time only (_TIME)	101	ACTION_DATETIME,CR_HX_TIME,SERVICE_TIME,START_TIME,STOP_TIME,ADMIT_DATETIME,INJURY_DATETIME,SPARCS_E
Time (_TM)	49	FIRST_TREAT_HOUR_TM,DISCHARGE_HOUR_TM,ADMSN_TM,DISCHG_TM,ADJUD_TM,DAILY_DIGEST_TM,ACQUISITION_TM,FRA

Each serves a specific purpose:

• _DTTM: Full datetime stamp
• _DT: Date only (often abbreviated)
• _DATE: Date only (spelled out)
• _TIME: Time portion only
• _REAL: Precise chronological ordering (as you learned)

Financial Amount Pattern (_AMT)

All monetary values use the _AMT suffix:

Explore financial amount columns

View SQL Query

SELECT 
    table_name,
    column_name,
    SUBSTR(documentation, 1, 100) || '...' as description
FROM _metadata
WHERE column_name LIKE '%_AMT'
  AND documentation LIKE '%dollar%' OR documentation LIKE '%amount%'
ORDER BY RANDOM()
LIMIT 5

table_name	column_name	description
SVC_LN_INFO_2	LN_POST_CLM_AMT	Stores Line Postage Claimed Amount...
HSP_CLP_CMS_LINE	ADJUSTMENT_AMT	Stores the adjustment amount for the claim line....
PMT_EOB_INFO_I	COB_AMT	The Coordination of�Benefits amount for that transaction....
CL_RMT_INP_ADJ_INF	CLM_DISP_SHAR_AMT	Monetary amount for the claim disproportionate share for inpatient adjudication information....
ORDER_MED	DOSAGE	The dispensation amount for the prescription entered by the user in the orders activity. This amount...

These are always:

• REAL data type
• Stored in dollars (not cents)
• Can be negative (credits, adjustments)
• NULL when not applicable

The Contact Serial Number (_CSN) Pattern

CSN (Contact Serial Number) is Epic’s unique identifier for encounters:

Understand CSN usage

View SQL Query

SELECT 
    column_name,
    COUNT(DISTINCT table_name) as used_in_tables
FROM _metadata
WHERE column_name LIKE '%_CSN%'
GROUP BY column_name
ORDER BY used_in_tables DESC
LIMIT 10

column_name	used_in_tables
PAT_ENC_CSN_ID	85
NOTE_CSN_ID	5
HX_LNK_ENC_CSN	3
TREATMENT_PLAN_CSN	2
PRIM_ENC_CSN_ID	2
PAT_CSN	2
EPT_CSN	2
CARE_PLAN_CSN_ID	2
UAC_LME_CSN	1
TR_TEAM_SRC_CSN_ID	1

CSNs are:

• Globally unique across the entire Epic system
• Never reused
• The primary way to link encounter-related data

Putting It All Together

Let’s see multiple patterns in one query:

See all patterns working together

View SQL Query

SELECT 
    -- ID/NAME pair
    ENTRY_USER_ID,
    ENTRY_USER_ID_NAME,
    
    -- _YN boolean
    CHRONIC_YN,
    
    -- _C_NAME category
    PROBLEM_STATUS_C_NAME,
    
    -- Standard date
    DATE_OF_ENTRY,
    
    -- The actual problem
    DESCRIPTION
    
FROM PROBLEM_LIST
WHERE PROBLEM_LIST_ID IS NOT NULL
LIMIT 5;

ENTRY_USER_ID	ENTRY_USER_ID_NAME	CHRONIC_YN	PROBLEM_STATUS_C_NAME	DATE_OF_ENTRY	DESCRIPTION
RAMMELZL	RAMMELKAMP, ZOE L	N	Active	8/29/2022 12:00:00 AM
DHILLOPS	DHILLON, PUNEET S	N	Active	8/9/2018 12:00:00 AM

Pattern Recognition Checklist

When encountering a new table:

1. Check for LINE columns → Indicates one-to-many relationships
2. Look for _HX suffix → Has historical tracking
3. Scan for _C_NAME → Contains denormalized categories
4. Find _REAL columns → Use these for chronological sorting
5. Identify _YN fields → Boolean flags with Y/N values
6. Spot ID/NAME pairs → Denormalized for convenience
7. Check for _2, _3 tables → Continuation of the base table

---

Key Takeaways

• _YN columns are Epic’s boolean pattern (Y/N/NULL only)
• ID/NAME pairs provide both referential integrity and human readability
• Continuation tables (_2, _3, _4) are parts of the same logical record
• Date patterns vary: _DTTM (datetime), _DT (date), _TIME (time only)
• _AMT indicates financial amounts in dollars
• _CSN columns contain Contact Serial Numbers for encounter linking
• These patterns combine with the major five to form Epic’s complete data grammar