Visit_occurrence

Reading from Synthea table encounters.csv

STEP 1

For encounterclass = ‘Inpatient’ (IP): 1. Sort data in ascending order by PATIENT, START, END. 2. Then by PERSON_ID, collapse lines of claim as long as the time between the END of one line and the START of the next is <=1 day. 3. Each consolidated inpatient claim is then considered as one inpatient visit, set 1. MIN(START) as VISIT_START_DATE 2. MAX(END) as VISIT_END_DATE 3. ‘IP’ as PLACE_OF_SERVICE_SOURCE_VALUE 4. See if any records with encounterclass ‘outpatient’ (OP), ‘ambulatory’ (OP), ‘wellness’ (OP), ‘emergency’ (ER) or ‘urgentcare’ (ER) occur during an identified ‘inpatient’ visit. These should be consolidated into the ‘inpatient’ visit, unless it is an ‘emergency’ or ‘urgentcare’ visit that starts and ends on the first day of the ‘inpatient’ visit. Types of outpatient (OP) visits not collapsed: 1. If an OP starts before an IP but ends during an IP 2. If an OP starts before and ends after an IP visit. If an OP is collapsed into an IP and its VISIT_END_DATE is greater than the IP’s VISIT_END_DATE it does not change the IP VISIT_END_DATE.

For claim type in (‘emergency’,‘urgentcare’) (ER) 1. Sort data in ascending order by PATIENT, START, END. 2. Then by PERSON_ID, collapse all (ER) claims that start on the same day as one ER visit, then take START as VISIT_START_DATE, MAX (END) as VISIT_END_DATE, and ‘ER’ as PLACE_OF_SERVICE_SOURCE_VALUE.

For claim type in (‘ambulatory’, ‘wellness’, ‘outpatient’) (OP) 1. Sort data in ascending order by PATIENT, START, END. 2. Then by PERSON_ID take START as VISIT_START_DATE, MAX(END) as VISIT_END_DATE, and ‘OP’ as PLACE_OF_SERVICE_SOURCE_VALUE.

Use logic found here (depending on cdm version - CDM v5.4 provided as example): https://github.com/OHDSI/ETL-Synthea/blob/main/inst/sql/sql_server/cdm_version/v540/AllVisitTable.sql

Destination Field Source field Logic Comment field
visit_occurrence_id all_visits.visit_occurrence_id
person_id patient Map by mapping person.person_source_value to patient. Find person.person_id by mapping encouters.patient to person.person_source_value.
visit_concept_id encounterclass When encounterclass is ‘emergency’ or ‘urgentcare’ then set to 9203. When encounterclass is ‘ambulatory’, ‘wellness’, or ‘outpatient’ then set to 9202. When encounterclass is ‘inpatient’ then set to 9201. Otherwise set to 0.
visit_start_date start
visit_start_datetime start
visit_end_date stop
visit_end_datetime stop
visit_type_concept_id Set all records as concept_id 32827 (EHR encounter record).
provider_id encounters.provider
care_site_id Set to null for all records
visit_source_value all_visits.encounters_id
visit_source_concept_id Set to 0 for all records
admitted_from_concept_id Set to 0 for all records
admitted_from_source_value Set to null for all records
discharge_to_concept_id Set to 0 for all records
discharge_to_source_value Set to null for all records
preceding_visit_occurrence_id lag(all_visits.visit_occurrence_id) over(partition by person.person_id order by all_visits.visit_start_date)