CDM Table: VISIT_OCCURRENCE

The VISIT_OCCURRENCE table contains all person visits to health care providers, including inpatient, outpatient and ER visits. A visit is an encounter for a patient at a point of care for duration of time. There could be several providers involved in the patient’s care during the Visit. In the past, Visits were identified directly from the MEDICAL_CLAIMS table. Now that there is a hierarchical relationship between Visit concepts we can build Visits off of the VISIT_DETAIL similar to how the DRUG_ERA table is derived from the DRUG_EXPOSURE table. See the VISIT_OCCURRENCE logic for more details.

VISIT_OCCURRENCE Logic

  • Using the terminal ancestor query map the VISIT_DETAIL_CONCEPT_IDs to their highest-level ancestor.
  • The highest-level ancestor concept_id will become the VISIT_CONCEPT_ID

Inpatient visits

  • Using the results of step 1, find all records in the VISIT_DETAIL table that roll up to the CONCEPT_ID 9201 (Inpatient Visit).
  • Sort the resulting VISIT_DETAIL records by PERSON_ID, VISIT_START_DATETIME, and VISIT_END_DATETIME.
  • Collapse records that have <= 1 day between them into one Visit and assign an autogenerated VISIT_OCCURRENCE_ID. This VISIT_OCCURRENCE_ID should be the primary key in the VISIT_OCCURRENCE table and the foreign key in the VISIT_DETAIL table (and several domain tables) for the records that were collapsed.
    • Give these Visits VISIT_CONCEPT_ID = 9201.
    • Set VISIT_START_DATETIME to min(VISIT_DETAIL_START_DATETIME)
    • Set VISIT_END_DATETIME to max(VISIT_DETAIL_END_DATETIME)

Emergency Room visits

  • After defining inpatient visits, find all VISIT_DETAIL records that roll up to the CONCEPT_ID 9203 (Emergency Room Visit)
  • Collapse records that have the same VISIT_DETAIL_START_DATETIME into one Visit.
    • If an emergency room visit starts on the first day of an Inpatient visit (defined in the step above), then
      • Assign the emergency room visit the autogenerated VISIT_OCCURRENCE_ID of the Inpatient visit.
      • Set VISIT_CONCEPT_ID = 262 (it would previously have been 9201).
      • Set VISIT_START_DATETIME = min(VISIT_DETAIL_START_DATETIME)
      • Set VISIT_END_DATETIME = max(VISIT_DETAIL_END_DATETIME)
    • If an emergency room visit occurs at any other point during an inpatient stay then assign the VISIT_OCCURRENCE_ID of the inpatient visit to the emergency room VISIT_DETAIL records and no VISIT_OCCURRENCE record should be generated.
    • Otherwise:
      • Assign the emergency room visit an autogenerated VISIT_OCCURRENCE_ID and create a VISIT_OCCURRENCE record.
      • Set VISIT_CONCEPT_ID = 9203.
      • Set VISIT_START_DATETIME = min(VISIT_DETAIL_START_DATETIME)
      • Set VISIT_END_DATETIME = max(VISIT_DETAIL_END_DATETIME)
  • The assigned VISIT_OCCURRENCE_IDS should be the primary key in the VISIT_OCCURRENCE table and the foreign key in the VISIT_DETAIL table for the records that were collapsed.

Rolling additional visit detail into Inpatient

  • For all other VISIT_DETAIL records, first look to see if they occur at any point within a previously defined inpatient visit. If so, assign the VISIT_OCCURRENCE_ID of the inpatient visit to the VISIT_DETAIL record.

Non-hospital institution visits

  • Using the remaining VISIT_DETAIL records and the results of step 1, find all records the CONCEPT_ID 42898160 (Non-hospital institution visit).
  • Sort the resulting VISIT_DETAIL records by PERSON_ID, VISIT_START_DATETIME, and VISIT_END_DATETIME.
  • Collapse records that have <= 1 day between them into one Visit and assign an autogenerated VISIT_OCCURRENCE_ID. This VISIT_OCCURRENCE_ID should be the primary key in the VISIT_OCCURRENCE table and the foreign key in the VISIT_DETAIL table (and several domain tables) for the records that were collapsed.
    • Give these Visits VISIT_CONCEPT_ID = 42898160.
    • Set VISIT_START_DATETIME to min(VISIT_DETAIL_START_DATETIME)
    • Set VISIT_END_DATETIME to max(VISIT_DETAIL_END_DATETIME)

      Rolling additional visit detail into Non-hospital institution visit

  • For all other VISIT_DETAIL records, look to see if they occur at any point within a previously defined non-hospital institution visit. If so, assign the VISIT_OCCURRENCE_ID of the non-hospital institution visit to the VISIT_DETAIL record.

All other VISIT_DETAIL records

  • For VISIT_DETAIL records that do not occur within an inpatient or non-hospital institution visit:
    • Sort records by PERSON_ID, VISIT_DETAIL_START_DATETIME, VISIT_DETAIL_END_DATETIME, and CARE_SITE_ID.
    • Collapse all records for the same person that have the same VISIT_DETAIL_START_DATETIME, VISIT_DETAIL_END_DATETIME, and CARE_SITE_ID.
    • Assign the visits an autogenerated VISIT_OCCURRENCE_ID and create a VISIT_OCCURRENCE record.
    • Set VISIT_CONCEPT_ID = terminal ancestor concept_id as defined above.
    • Set VISIT_START_DATETIME = min(VISIT_DETAIL_START_DATETIME)
    • Set VISIT_END_DATETIME = max(VISIT_DETAIL_END_DATETIME)
    • The assigned VISIT_OCCURRENCE_IDS should be the primary key in the VISIT_OCCURRENCE table and the foreign key in the VISIT_DETAIL table for the records that were collapsed.
Destination Field Source Field Applied Rule Comment
VISIT_OCCURRENCE_ID   System generated.  
PERSON_ID VISIT_DETAIL PERSON_ID    
VISIT_CONCEPT_ID See the logic above for rolling up VISIT_DETAIL VISIT_DETAIL_CONCEPT_ID the the terminal ancestor concept_id.    
VISIT_START_DATE VISIT_DETAIL VISIT_DETAIL_START_DATE Use min(VISIT_DETAIL_START_DATE)  
VISIT_START_DATETIME Set time to 00:00:00 UTC tz

VISIT_DETAIL VISIT_DETAIL_START_DATETIME		 Use min(VISIT_DETAIL_START_DATETIME)  
VISIT_END_DATE VISIT_DETAIL
VISIT_DETAIL_END_DATE		 Use max(VISIT_DETAIL_END_DATE)  
VISIT_END_DATETIME Set time to 00:00:00 UTC tz

VISIT_DETAIL
VISIT_DETAIL_END_DATETIME		 Use max(VISIT_DETAIL_END_DATETIME)  
VISIT_TYPE_CONCEPT_ID   Use concept 32810 (Claim)  
PROVIDER_ID   Use the VISIT_DETAIL records to find provider associated with a visit. If there are more than one provider_id, then sort by inpatient>emergency room>outpatient and pick the first occurrrence by date.  
CARE_SITE_ID VISIT_DETAIL CARE_SITE_ID    
VISIT_SOURCE_VALUE   This will be blank though the VISIT_OCCURRENCE_ID can be linked back to VISIT_DETAIL which can then be linked to the source through the VISIT_DETAIL_SOURCE_VALUE  
VISIT_SOURCE_CONCEPT_ID   0  
ADMITTING_SOURCE_CONCEPT_ID   0  
ADMITTING_SOURCE_VALUE   NULL  
DISCHARGE_TO_CONCEPT_ID      
DISCHARGE_TO_SOURCE_VALUE VISIT_DETAIL
DISCHARGE_TO_SOURCE_VALUE		    
PRECEDING_VISIT_OCCURRENCE_ID VISIT_OCCURRENCE For a given person, find the previous visit and reference it. A foreign key to the VISIT_OCCURRENCE table of the visit immediately

Common Data Model ETL Mapping Specification for Optum Extended SES & Extended DOD
CDM Version = 5.4.0, Clinformatics Version = v9.0

3-Nov-2023

  • Clinformatics Version = v8.0 -> v9.0
  • CDM v6.0 -> V5.4

Please contact Clair Blacketer (https://github.com/clairblacketer) if you have any questions