Record diagnosis, symptoms, pathological signs, condition_concept_id vs source_value, condition_status (admitting/primary/secondary), link to Visit and Provider, distinguish from OBSERVATION table.
Introduction
CONDITION_OCCURRENCE records all medical diagnoses, symptoms, and signs that the doctor notes for the patient. This is often the most analyzed table in OMOP CDM — because medical research often starts with the question: "Who gets what?"
1. Table structure
Column
Type
Required
Description
condition_occurrence_id
INTEGER
✅ PK
Unique ID
person_id
INTEGER
✅ FK
Patients
condition_concept_id
INTEGER
✅
Standard Concept (SNOMED)
condition_start_date
DATE
✅
Diagnosis start date
condition_start_datetime
DATETIME
Date and time
condition_end_date
DATE
Date of end of diagnosis
condition_end_datetime
DATETIME
End date and time
condition_type_concept_id
INTEGER
✅
Data source
condition_status_concept_id
INTEGER
Status (Primary, Admitting...)
stop_reason
VARCHAR(20)
Reasons for stopping diagnosis
provider_id
INTEGER
FK
Diagnosis Doctor
visit_occurrence_id
INTEGER
FK
Related Visit
visit_detail_id
INTEGER
FK
Visit details (which department)
condition_source_value
VARCHAR(50)
Original code (eg "E11")
condition_source_concept_id
INTEGER
Original concept
condition_status_source_value
VARCHAR(50)
Original status
2. What is stored in CONDITION_OCCURRENCE?
2.1. SHOULD save
Type
Example
Vocabulary
Disease diagnosis
Type 2 diabetes, Pneumonia
SNOMED CT
Symptoms
Fever, Abdominal pain, Cough
SNOMED CT
Clinical signs
Swollen feet, Jaundice
SNOMED CT
Differential diagnosis
Suspected pulmonary tuberculosis
SNOMED CT
2.2. DO NOT save (save in another table)
Type
Destination table
Reason
"History of diabetes"
OBSERVATION
Domain = Observation
"No allergies"
OBSERVATION
Absence → Observation
"BMI = 28"
MEASUREMENT
Domain = Measurement
Drug side effects
CONDITION + DRUG_EXPOSURE
Condition for ADR, Drug for drug causing
3. condition_status_concept_id — Diagnostic status
-- SQL ETL
SELECT
ROW_NUMBER() OVER() AS condition_occurrence_id,
pm.person_id,
COALESCE(cr.concept_id_2, 0) AS condition_concept_id,
cd.ngay_chandoan AS condition_start_date,
NULL AS condition_end_date,
32817 AS condition_type_concept_id,
CASE cd.loai_chandoan
WHEN 'CHINH' THEN 32902 -- Primary
WHEN 'PHU' THEN 32908 -- Secondary
ELSE 0
END AS condition_status_concept_id,
cd.ma_icd10 AS condition_source_value,
COALESCE(c_source.concept_id, 0) AS condition_source_concept_id
FROM chandoan_his cd
JOIN person_mapping pm ON cd.ma_bn = pm.source_id
LEFT JOIN concept c_source
ON cd.ma_icd10 = c_source.concept_code
AND c_source.vocabulary_id = 'ICD10CM'
LEFT JOIN concept_relationship cr
ON c_source.concept_id = cr.concept_id_1
AND cr.relationship_id = 'Maps to'
LEFT JOIN concept c_std
ON cr.concept_id_2 = c_std.concept_id
AND c_std.standard_concept = 'S';
4.2. Vietnam-specific data processing
Problem
Solution
ICD-10-VN is different from ICD-10-CM
Mapping via SOURCE_TO_CONCEPT_MAP
BV internal code
Usagi mapping tool
Missing end date
condition_end_date = NULL (valid)
1 ICD map many SNOMED
Choose the most suitable concept
5. Distinguish between CONDITION and OBSERVATION
Criteria
CONDITION_OCCURRENCE
OBSERVATION
Content
Current illness/under treatment
Prehistory, lifestyle, records
Example
"Type 2 diabetes"
"Family history of diabetes"
Domain
Conditions
Observation
Standard Vocab
SNOMED CT
SNOMED CT
When?
Active disease
Record information
Rule: Always check Standard Concept's domain_id on Athena. If domain = "Observation", save to OBSERVATION even though the source is ICD-10.
6. Popular SQL analysis
-- Top 10 chẩn đoán phổ biến nhất
SELECT
c.concept_name AS condition_name,
COUNT(DISTINCT co.person_id) AS patient_count,
COUNT(*) AS record_count
FROM condition_occurrence co
JOIN concept c ON co.condition_concept_id = c.concept_id
WHERE co.condition_concept_id != 0
GROUP BY c.concept_name
ORDER BY patient_count DESC
LIMIT 10;
-- Tỉ lệ mắc bệnh theo giới tính
SELECT
g.concept_name AS gender,
c.concept_name AS condition_name,
COUNT(DISTINCT co.person_id) AS patients
FROM condition_occurrence co
JOIN person p ON co.person_id = p.person_id
JOIN concept g ON p.gender_concept_id = g.concept_id
JOIN concept c ON co.condition_concept_id = c.concept_id
WHERE co.condition_concept_id = 201826 -- Type 2 DM
GROUP BY g.concept_name, c.concept_name;
-- Comorbidity: BN tiểu đường có tăng huyết áp?
SELECT
COUNT(DISTINCT co_dm.person_id) AS dm_patients,
COUNT(DISTINCT co_ht.person_id) AS dm_with_hypertension,
ROUND(
COUNT(DISTINCT co_ht.person_id) * 100.0 /
NULLIF(COUNT(DISTINCT co_dm.person_id), 0), 1
) AS comorbidity_pct
FROM condition_occurrence co_dm
LEFT JOIN condition_occurrence co_ht
ON co_dm.person_id = co_ht.person_id
AND co_ht.condition_concept_id IN (
SELECT descendant_concept_id
FROM concept_ancestor
WHERE ancestor_concept_id = 320128 -- Essential hypertension
)
WHERE co_dm.condition_concept_id IN (
SELECT descendant_concept_id
FROM concept_ancestor
WHERE ancestor_concept_id = 201826 -- Type 2 DM
);
Summary
CONDITION_OCCURRENCE = diagnosis, symptoms, signs of pathology
condition_concept_id uses Standard Concept (SNOMED CT)
