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
| Destination Field | Source field | Logic | Comment field |
|---|---|---|---|
| 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 44818517. | ||
| provider_id | |||
| care_site_id | |||
| visit_source_value | encounterclass | ||
| visit_source_concept_id | |||
| admitted_from_concept_id | |||
| admitted_from_source_value | |||
| discharge_to_concept_id | |||
| discharge_to_source_value | |||
| preceding_visit_occurrence_id |