Lesson 9: PROCEDURE_OCCURRENCE — Procedures & Surgery
Record procedures, surgeries, and medical interventions. SNOMED / CPT4 / ICD-10-PCS mapping, modifier_concept_id, distinguishing Procedure vs Measurement vs Drug, practicing ETL of Vietnamese hospital data.
Introduction
PROCEDURE_OCCURRENCE records all procedures, surgeries, and medical interventions performed on a patient. From simple blood pressure measurements to complex open heart surgery — it's all standardized in this table. The lesson will help you understand when to use PROCEDURE, when to use MEASUREMENT or DRUG.
1. Table structure
Column
Type
Required
Description
procedure_occurrence_id
INTEGER
✅ PK
Unique ID
person_id
INTEGER
✅ FK
Patients
procedure_concept_id
INTEGER
✅
Standard Concept
procedure_date
DATE
✅
Implementation date
procedure_datetime
DATETIME
Date and time
procedure_end_date
DATE
End date (new CDM 5.4)
procedure_end_datetime
DATETIME
Closing time
procedure_type_concept_id
INTEGER
✅
Data source
modifier_concept_id
INTEGER
Add (left/right, 2nd...)
quantity
INTEGER
Number of executions
provider_id
INTEGER
FK
Doctor performed
visit_occurrence_id
INTEGER
FK
Related Visit
visit_detail_id
INTEGER
FK
Visit details
procedure_source_value
VARCHAR(50)
Original code
procedure_source_concept_id
INTEGER
Original concept
modifier_source_value
VARCHAR(50)
Original Modifier
New in CDM 5.4:procedure_end_date and procedure_end_datetime — important for surgery that lasts many hours/days.
Golden Rule: Always check the domain_id of Standard Concept. Even though the source code is CPT4 (procedure code), if Standard Concept has domain = Measurement, it must be saved in MEASUREMENT.
3. Procedure vs Measurement vs Drug — Boundaries
Original data
Where?
Reason
Appendicitis surgery
PROCEDURE
Domain = Procedure
Abdominal ultrasound
PROCEDURE
Domain = Procedure (imaging)
Blood test (result 5.8 mmol/L)
MEASUREMENT
Has a measurement value → Measurement
Cardiopulmonary X-ray (no results)
PROCEDURE
Imaging → Procedure
Insulin injection
DRUG_EXPOSURE
Drug administration
Colonoscopy + biopsy
PROCEDURE
Procedural intervention
Physical therapy 30 minutes
PROCEDURE
Procedure domain
Blood transfusion
PROCEDURE
Transfusion = Procedure
4. modifier_concept_id — Additional information
Concept ID
Modifiers
Meaning
4148525
Left
Left
4149625
Right
Right
4236436
Bilateral
Both sides
4215561
Initial encounter
First time
4215562
Subsequent encounter
Re-examination
-- Phẫu thuật cắt ruột thừa nội soi (bên phải)
INSERT INTO procedure_occurrence (
procedure_occurrence_id, person_id, procedure_concept_id,
procedure_date, procedure_end_date,
procedure_type_concept_id,
modifier_concept_id, quantity,
provider_id, visit_occurrence_id,
procedure_source_value
) VALUES (
90001, 100001, 44783086, -- SNOMED: Lapar appendectomy
'2024-06-15', '2024-06-15',
32817, -- EHR
4149625, 1, -- Right side, 1 time
5001, 50001,
'0DTJ4ZZ' -- ICD-10-PCS
);
5. ETL VN data
5.1. Popular data sources
Source
Description
Original Vocabulary
List of hospital technical services
BV technical services
Internal code
ICD-9-CM Proc
Old surgery code
ICD9Proc
List of social insurance
Social insurance service code
Internal insurance code
5.2. SQL ETL
SELECT
ROW_NUMBER() OVER() AS procedure_occurrence_id,
pm.person_id,
COALESCE(cr.concept_id_2, 0) AS procedure_concept_id,
tt.ngay_thuchien AS procedure_date,
tt.ngay_ketthuc AS procedure_end_date,
32817 AS procedure_type_concept_id,
0 AS modifier_concept_id,
tt.so_lan AS quantity,
tt.ma_dvkt AS procedure_source_value,
COALESCE(c_source.concept_id, 0) AS procedure_source_concept_id
FROM thuthuat_his tt
JOIN person_mapping pm ON tt.ma_bn = pm.source_id
LEFT JOIN source_to_concept_map stcm
ON tt.ma_dvkt = stcm.source_code
AND stcm.source_vocabulary_id = 'VN_PROCEDURE'
LEFT JOIN concept c_std
ON stcm.target_concept_id = c_std.concept_id
AND c_std.standard_concept = 'S'
AND c_std.domain_id = 'Procedure' -- ← Chỉ Procedure domain!
LEFT JOIN concept c_source
ON tt.ma_dvkt = c_source.concept_code
LEFT JOIN concept_relationship cr
ON c_source.concept_id = cr.concept_id_1
AND cr.relationship_id = 'Maps to';
5.3. Handles domain routing
-- DVKT "Xét nghiệm HbA1c" → mã CPT 83036
-- Standard concept domain = Measurement → KHÔNG lưu vào PROCEDURE
-- Bước 1: Tìm Standard Concept
SELECT c.*
FROM concept c
JOIN concept_relationship cr ON c.concept_id = cr.concept_id_2
AND cr.relationship_id = 'Maps to'
JOIN concept c_src ON cr.concept_id_1 = c_src.concept_id
WHERE c_src.concept_code = '83036'
AND c_src.vocabulary_id = 'CPT4';
-- domain_id = 'Measurement' → route sang MEASUREMENT table
-- Bước 2: Lưu vào đúng bảng
-- Nếu domain = 'Procedure' → procedure_occurrence
-- Nếu domain = 'Measurement' → measurement
-- Nếu domain = 'Observation' → observation
-- Nếu domain = 'Drug' → drug_exposure
6. SQL analysis
-- Top 10 thủ thuật phổ biến
SELECT
c.concept_name AS procedure_name,
COUNT(*) AS procedure_count,
COUNT(DISTINCT po.person_id) AS patient_count
FROM procedure_occurrence po
JOIN concept c ON po.procedure_concept_id = c.concept_id
WHERE po.procedure_concept_id != 0
GROUP BY c.concept_name
ORDER BY procedure_count DESC
LIMIT 10;
-- Thống kê phẫu thuật theo tháng
SELECT
DATE_TRUNC('month', po.procedure_date) AS month,
c.concept_name AS procedure_name,
COUNT(*) AS total
FROM procedure_occurrence po
JOIN concept c ON po.procedure_concept_id = c.concept_id
WHERE po.procedure_concept_id = 44783086 -- Appendectomy
GROUP BY month, c.concept_name
ORDER BY month;
-- BN có cả chẩn đoán + phẫu thuật liên quan
SELECT
po.person_id,
co_cond.concept_name AS diagnosis,
co_proc.concept_name AS procedure_name,
co.condition_start_date,
po.procedure_date
FROM procedure_occurrence po
JOIN condition_occurrence co ON po.person_id = co.person_id
AND po.visit_occurrence_id = co.visit_occurrence_id
JOIN concept co_cond ON co.condition_concept_id = co_cond.concept_id
JOIN concept co_proc ON po.procedure_concept_id = co_proc.concept_id
WHERE co.condition_concept_id = 441604 -- Appendicitis
AND po.procedure_concept_id = 44783086 -- Appendectomy
LIMIT 20;