OHDSI Home | Forums | Wiki | Github

Building Macro visits using Micro visits data

Hello Everyone,

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

a) visit_occurrence
b) visit_detail

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?

@MPhilofsky @DTorok - Can I kindly request your help on this? I reach out to you because I referred your previous posts on visit tables.

Others are also welcome to help us.

Hi @Akshay,

Your line of thinking about Visits is off. Visits are independent of Conditions. A Visit is a single encounter/appointment/inpatient visit/ER visit/checkup/etc. where a Person “continuously receives medical services from one or more providers at a Care Site in a given setting within the health care system”.

By doing this:


[quote=“Akshay, post:1, topic:11805”]

It looks like the Person had a Visit from 1/8/2013 to 30/8/2013 and then another from 11/8/2015 to 30/8/2015. You shouldn’t do this.

Our source data has the opposite problem. We have multiple encounter identifiers for one actual Visit.

The first question to always ask is what is your use case? What are the questions you are trying to answer?

For EHR data, the Visit Detail table is generally used to record the dates/times a Person was in different departments for an inpatient Visit Occurrence record. The current community use case for Visit Detail is to know which Persons were in an Intensive Care Unit (this is the Visit Detail for the Visit Occurrence record), for how long, linked to Procedures/Devices/other clinical events, outcomes for an Inpatient Visit for covid-19. The Visit Detail records the dates/times and can link to the other clinical event tables.

Looking at your data, I don’t see any Visit Detail records. Visit Detail is an optional table

Hi @MPhilofsky,

Appreciate your response. Just a follow-up question. forgive me if it seems redundant… I am sharing a sample of how my source data looks like to help you better understand the problem.

From the above screenshot, we can infer that patient 321456 visited the hospital as an Inpatient for Urinary Tract Infection condition on 1st Aug of 2013. He was admitted for two days till 3/8/2013.

But we can see that he has come to the hospital again 4 times on different days but for the same condition. Might be he had some complications which made him be admitted to ICU or some complications which made him to be admitted in normal ward (but not in ICU) etc. Basically, what I am trying to convey is his future admissions related to the parent visit that he had on 1st Aug 2013, can be put into visit_detail. Am I right?

Scenario - 1 - First record in visit_occurrence and rest of the related visits in visit_detail

Does this seem okay?

Scenario - 2

a) Create individual visit occurrence records based on raw visit data

but the problem here is my raw clinical data (drugs/diagnosis/labs etc) has an accompanying encounter_id (visit_occurrence_id) in the form which has only 1st 4 chars which is ABCD. You can refer raw data shown above for example

So when I wish to extract diagnosis for a specific visit/encounter say ABCD, this might result in 0 records because of a mismatch between ABCD and ABCD01 (ABCD <> ABCD01).

b) ** store raw visit records as is**

The problem here is Visit_occurrence_id is a primary key column, so I cannot retain or put all my raw visit information as is into visit_occurrence table because it violates primary key constraint (duplicates not allowed) as shown below

q1) So the only way to store our raw data (which looks like as shown in the sample above), is by following scenario 1? irrespective of whether its an ICU transfer within the same visit or discharged once and again come back for the same condition (be admitted in ICU or in any other ward). I am not sure whether there is any other way to store this info. I don’t have any use-case at the moment but first, we would like to know whether OMOP CDM is feasible to store our data appropriately without loss of information.

q2) can you let us know how you solved the below issue at your site?

You should look at the proposed extension for the Episode and Episode event tables http://ohdsi.github.io/CommonDataModel/oncology.html. This will allow you to treat episodes and visits independently instead of trying to capture the episode information in the Visit Occurrence table.

1 Like

I think I understand your thought process. You have a series of encounters for one clinical event. As @DTorok suggested, you can group these using the Episode and Episode Event tables: “The EPISODE table aggregates lower-level clinical events (VISIT_OCCURRENCE, DRUG_EXPOSURE, PROCEDURE_OCCURRENCE, DEVICE_EXPOSURE) into a higher-level abstraction representing clinically and analytically relevant disease phases/outcomes and treatments. The EPISODE_EVENT table connects qualifying clinical events (VISIT_OCCURRENCE, DRUG_EXPOSURE, PROCEDURE_OCCURRENCE, DEVICE_EXPOSURE) to the appropriate EPISODE entry.” CDM V6 will allow you to connect all your encounters/Visits based on a clinical event.

In OMOP the “parent” Visit is an Inpatient/Outpatient/ER/etc. encounter. The “child” Visit Detail might be a lab or imaging encounter which occurred during the parent visit or it could be the ER to ICU to medical floor encounters which occurred during the parent Inpatient Visit. Based on your examples above, you do not have Visit Details. Visit Details occur during Visit Occurrences. You have Visits, each source row is a Visit. The Visits are all related to one Episode, which is a grouper for the UTI Visits, and the Episode Event, is the UTI Condition.

Our source will have multiple encounters occurring at the same start datetime. Example: ER, Imaging, and Lab will all be separate rows at the source with the same datetime for a Person. This is one ER Visit that included a trip to the lab department and a trip to the Imaging department during the ER Visit. We are going to use date times to find the parent Visit and make the Lab & Imaging Visits the child Visit Detail records.

1 Like

@MPhilofsky - There isnt anyway to store my visit information (episode details) in CDM V5.3.1 ?

Well, you could link all the like Visits using the Fact Relationship table in v5.3.1. This is an ugly solution. I don’t think there is a relationship_id for this, so you would need to request it.


The Visits can group all the encounters (micro-visits) that happen from beginning of the hospitalization (or whatever the visit is) from admission to discharge. All goes in there. If you need a larger umbrella construct of treating a patient for a disease you use the Episodes.

If you are behind in the version you can always use future model artefacts. In other words, you can create a Frankenstein model and use the Episode table in a V5 CDM. However, no tool and no methods package from the outside will be able to see it. You can do your own research, but network research requires compliance with the model. And when you upgrade to V6 you are already ready with the new table.

Makes sense?

1 Like