Finding max/min values with overlapping time

Hi all--long time Alteryx user but first time posting. I'm wondering if you could help me figure out a way to determine the amount of patients at any one time in our emergency department using start and end times.

Basically, if you were a patient, I want to know how many other patients are in the ED during your visit. I'm having a lot of trouble with the calculations.

Thanks!

PatientID	Arrival	Departure
1	10:00	10:33
2	10:00	10:45
3	10:15	10:45
4	10:50	11:15
5	10:43	10:59
6	10:57	11:20

Desired Output

PatientID	Arrival	Departure	Max in Dept Min in Dept
1	10:00	10:33	3 2
2	10:00	10:45	3 2
3	10:15	10:45	3 3
4	10:50	11:15	3 1
5	10:51	10:59	3 2
6	10:57	11:20	3 1

Common Use Cases

Accepted answers

Claje

I would use a better option for getting your fields set up than the one I used in this example, but I'm hoping it gives you some ideas for the comparison.

Basically I would create a record for each patient at every minute of their stay, and join this recordset to itself. Then I would summarize this by minute to get a count of patients in the ED at any point in time, and then summarize again to get the max and min of this value per patient.

Hope the attached helps!

claje_timestampcompare.yxmd

JohnJPS

While those are all present at some point along with patient 4, you can see (for instance) that 8 arrives after 6 has left, so they are not all in the room at the same time. Alteryx is calculating the max number overlapping all at the same time: so, during 4's stay, the most there all at the same time is 4. It would be easy to instead calculate a count of total people that had any overlap with 4, but I didn't think that was the goal.

All comments

Claje

I would use a better option for getting your fields set up than the one I used in this example, but I'm hoping it gives you some ideas for the comparison.

Hope the attached helps!

claje_timestampcompare.yxmd

JohnJPS

I tried another common approach, which avoids the self-join, but does use a few more tools; basically generating rows from minTime to maxTime (overall, not by patient) and joining that back and summarizing number of patients present at any given time, etc.

Depending on data size vs. length of overall time-frame, you can make the best choice.

CountOverlappingAppts.yxmd

jeffwiswell

JohnJPS--thanks so much for helping! It seems like the total counts are off, though

jeffwiswell

Thanks Claje!!! That helped a lot. I forgot to put it in originally, but can you help for the case where the patient may arrive one evening and be discharged early into the next morning? I've attached an excel file for an example

Max Min Example.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703