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 VISIT and ENCOUNTER 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 above, 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_DATE, VISIT_DETAIL_END_DATE, and VISIT_DETAIL_CONCEPT_ID.
    • Collapse all records for the same person that have the same VISIT_DETAIL_START_DATETIME, VISIT_DETAIL_END_DATETIME, and VISIT_DETAIL_CONCEPT_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_DATE)
    • Set VISIT_END_DATETIME = max(VISIT_DETAIL_END_DATE)
    • 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 32827 (EHR encounter record)  
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

Change Log:

8/19/2020

  • Visits are now built using the same logic as other databases by aggregating Visit_Detail records. The Visit table in Optum_EHR is just an Optum construct by using encounters. Not every encounter has a Visit and the dates are often different than those of the encounters they are associated to.

8/01/2020

  • Visits are built similarly to how they were before except interaction_time is not taken into account. Interaction_time will now be stored in the VISIT_DETAIL records associated with a VISIT_OCCURRENCE

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