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.