I have data where each record represents an inpatient ward stay at a particular hospital ward. A patient may have multiple inpatient ward stays and change ward within an inpatient stay.
There are different types of ward (serving the same function) and there are multiple wards of the same type.
When a patient moves to another ward of the same type on the same day or the next day it is classed as the same inpatient spell.
When a patient moves to another ward but it not on the same day or the next day, this constitutes the start of a new inpatient spell.
When a patient moves to another ward of a different type, regardless of whether it was on the same or next day, this constitutes the start of a new inpatient spell
I am trying to calculate a summary, at individual patient level by inpatient spell. The attached data provides two example patients, showing the source data records and the expected/required output.
First of all, thanks for an example with sample data and expected outcome. That made the question much easier to understand.
I believe the most tricky part here is identifying consecutive stays. Once we can band consecutive stays together, we can aggregate easily.
As per your comment in the workbook, stays are consecutive when the Ward Type is the same and the Start Date of the subsequent Ward Stay is on the same day. Apparently, patient ID doesn’t change during a consecutive stay.
In EasyMorph comparison with previous rows is done using the “Shift columns” action that can bring previous row values one level down as a new column, then comparing values with this new column.
With a few rules, we can identify consecutive stays. Aggregating them is the easiest part.
See below a project that does the transformation you need. I’ve left comments with explanations to some actions.
