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: https://github.com/OHDSI/ETL-Synthea/blob/master/ETL/SQL/AllVisitTable.sql