I already referred these posts but just quite unclear and would like to seek your views before we implement the logic at our site.
In our source visit data, each patient has an encounter_id.
For ex: In the below screenshot, we can see that Patient’s encounter_id starts with
ABCD. This means that patient has visited the hospital (as either inpatient/outpatient) for a specific condition called
condition_1. He later visited the hospital for the same condition 4 more times. We say it is for the same condition because the encounter_id helps us figure out it’s for the same/related visit (meaning it could be a follow-up visit, visiting for a lab test, visiting for another check-up, visiting pharmacy counter, transfer, etc)
Later the same patient (after 2 years), have visited the hospital for different condition called
condition_2 which we can find out by using
XYZE (different from ABCD) and he has followed up with 4 more visits related to the same condition.
Now my question is we have two tables
Scenario 1 - Visit_occurrence table (am showing only a few columns)
I have created artificial visit numbers based on the timing of visits (because visit_occurrence_id) is a primary key. So I can’t store original encounter_id as is. Hence I add the visit_numbers as shown below.
However, there might be a problem with this
Because all my lab, drugs, and conditions have the corresponding encounter_id as ABCD, XYZE etc… There is no visit_number component. So when we try to extract data for a specific visit, we might get 0 records because of no match
Scenario 2 - Visit_occurrence table & visit detail table (am showing only a few columns)
Should I build macro-level visit information (of visit ABCD & XYZE)? I know the patient had 5 (micro) visits in total for
condition_1. So I get the 1st visit_start_date and last visit_end_date for
ABCD related visits. Similarly, I do for XYZE related visits as shown below
And in visit_detail I store micro-level information as shown below
q1) So, am I right to proceed with scenario 2?
q2) Is there any other approach that people here follow/adopt during their CDM transformation?
q3) What would happen if I modify scenario 2 to include only
Visit_occurrence_table as shown below and don’t fill the visit_detail table?
modified scenario 2
q4) I understand I lose the detailed information on visits but based on your experience do you think that information is helpful?
q5) I am new to healthcare ETL and trying to understand how it will impact if I don’t retain them. Are there any use-cases, cohort examples in public ATLAS, or OHDSI tutorials in Youtube that show the use of visit detail table? Am not able to imagine how storing this information is helpful for analysis when you anyway know the patients’ visit duration (through visit_occurrence_table)?
Can you help me with this?