OHDSI Home | Forums | Wiki | Github

What is sudden decline - Visit end date for Inpatients?


(Akshay Kumar) #1

Hello Everyone,

I did read the cdm doc in github for visit_end_date logic for Inpatients who don’t have visit_end_date.

But may I know what does it mean by sudden decline of activity or from the absence of inpatient procedures/drugs.

Can anyone who has done ETL help us on how did you do this?

For example, I have an Inpatient whose visit_end_date is missing. but when I look at his drug_exposure, I am able to see that he has records consistently for 2-3 years.

Is there any example or SQL snippet that you can share for me to understand how this is done?

(Christian Reich) #2

Well, nobody is in the hospital for 2-3 years. Hopefully not. Those drugs the patient probably got prescribed in an ambulatory setting. So, in “sudden decline of activity or from the absence of inpatient procedures/drugs” the emphasis is on inpatient.

(Akshay Kumar) #3

Hi @Christian_Reich,

Yes. You are right. But now I did verify that this patient doesn’t have any inpatient drugs for the corresponding visit_occurrence_id. So how do we define visit_end_date now?

And our data source doesn’t consist of patient procedures. So it’s missing

(Christian Reich) #4

So, you don’t know when the patient leaves the hospital, right? And you don’t have any data while the patient is in hospital? What do you have?

(Akshay Kumar) #5

Yes, I don’t know when the patient left the hospital. I don’t have drugs and procedures.

Only other data tables that I have is condition and measurement.

When I ran a simple SQL, I see that I have 212 visit_occurrence_ids (210 unique) without visit_end_dates (for inpatients) . Or 212 person records (178 person_ids) without visit_end_dates (for inpatients).

When I tried to find their corresponding condition information, I see there are only 6 condition occurrence records corresponding to these unique visit_occurrence_ids (meaning 6 unique visit_occurrence_ids of Inpatients with no visit_end_date leads to 6 unique patient records in condition_table)

When I tried to find their corresponding measurement information, I see there are only 75 measurement records. Out of 75, there are 6 unique patients or 6 unique visit_occurrence_ids same as above.

(Qi Yang) #6


Since I don’t know what your source data look like, it is impossible to give you specific advice. But when a patient stays in the hospital, the database should have a wealth of activities. Vital signs (blood pressure, heart rate, body temperature and oxygen level etc. ) are recorded on a daily basis or even hourly basis. Intravenous fluid are given constantly. ECG is fairly frequently monitored. And as @Christian_Reich mentioned, procedural drugs are given very often and they are usually recorded in the source data as procedures (usually using HCPCS, CPT4, and ICD9Proc etc.) instead of drugs (NDC codes). All these activities should suddenly drop when a patient left the hospital.

(Akshay Kumar) #7

Hi @QI_omop,

Like I mentioned above, Out of 178 Inpatients with no visit_end_dates, I see the measurement records only for 6 patients. For the remaining, I don’t have any info on any of the tables. So I guess it is a data issue?


Second, For example let’s say patient A is admitted in hospital on Jan 1st 2008. Like you said, let’s say he has measurements records (vital signs) till Jan 8th or Drug exposure records till Jan 8th. So we pick the latest event that has occurred in patient’s treatment/hospital journey. In this case, should we just pick Jan 8th or 9th as visit_end_date?

Q1) So do I have to drop all those 172 (178 minus 6) patients from my dataset? Because they don’t have any corresponding info on condition,drugs, procedures etc. So it’s likely to be a data issue? Any suggestions on this based on your experience

Q2) Is my logic to derive Visit_end_date is right?

(Qi Yang) #8


I am talking about source table, not OMOP CDM table. What source tables do you have? Do you have tables like event, vital signs, lab test? What kind of codes does procedure table use, HCPCS, CPT4 etc? What kind of data asset are you trying to convert, Cerner, Epic etc.? Without a thorough understanding of the source tables, any advice would be meaningless.

(Akshay Kumar) #9

Hi @QI_omop - Thanks for the response. Our raw data is from EHR system as csv files. We don’t have raw data for procedures. But yes, we do have raw data for lab test/measurement , conditions data, visit_data, drug data. This is all we have.

(Qi Yang) #10


In the source drug table, what kind of coding do you see? NDC, or HCPCS, CPT4 or something else? Also are there tables called Report, Encounter, Event, Discharge, or Clinical Event tables in your source?

(Akshay Kumar) #11

Hi @QI_omop,

ATC code for drugs.

We don’t have report.

We do have enounter data which is visit data because the raw encounter file contains the hospital_admit_date,hospital_discharge date, encounter id etc.

For clinical events, we have list of diseases that patients were diagnosed for and medical lab test/measurement results

EMR is their own EMR. It’s neither cerner nor Epic

Hope it helps

(Qi Yang) #12


These information are not helpful. My advice is if 178 is only a small percentage of total population, then don’t spend time investigating them. Just come up with an arbitrary rule, e.g, add 7 days, 2 weeks or 30 days to the visit start date for visit end date. However, if the 178 represents a large percentage of the total population, then you should ask the data owner (whoever provides your the data) and come up with a logic to determine visit end date.

(Melanie Philofsky) #13

I would like to add the above to my OHDSI wish list :slight_smile: Missing visit end dates are a common problem for EHR data.

Take the date of the last clinical event. Jan. 8th.

What question can you answer without any data for a Visit? I wonder if the Visit really occurred? Maybe this was a scheduled Visit (surgery was scheduled for this day) and the Visit never occurred?

Before you derive anything, you should speak to an expert on the source data. They should be able to give you further insight into the missing Visit end date.

You should join the EHR WG details are located here We discuss all things OHDSI, healthcare, EHR, and ETL.

@QI_omop The vast majority of Persons do not stay in the hospital for a week, 2 weeks or a whole month. Very rare.

(Chris Knoll) #14

You can use era building logic to identify those events which should be grouped together. i’ve created a demo gist here which explains the algorithm. Note: this is a very complicated piece of SQL, if you are not proficient in SQL, you may have many questions that we won’t be able to answer.

The strategy is:
combine EHR events together into ‘episodes’ where if an event is within 2 days (arbitrary window), you ‘collapse’ them together into a continuous period of time. These episodes will be used to determine your visit end dates.

Then you take your visit starts and apply them to those episodes to divide those episodes into distinct visit duration. Some considerations to think about:

  • How close should a visit start to an event episode to be considered the visit start for the specific event episode? 2 days? 3 days? the visit must start ont he exact date you derived the event episode?
  • if you have a visit start in the middle of an event episode, should that event episode be split into 2 different visits?
  • etc etc.

There are quite a few posts about era building logic made in the forums, if you want to search for ‘era’, you will find some help there. But, I strongly suggest, if you want to use this SQL approach, you try it yourself with some sample data to understand how different event records get combined into continuous duration of time.