Finding max/min values with overlapping time

[jeffwiswell]

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

[Claje · Accepted solution]

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!

[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.

[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

[JohnJPS]

I think Alteryx is right... at 10:40, there are only 2 people present, so the "Min in Dept" during Patient 3's stay is 2, not 3 (as shown in the expected output).  @Claje's approach  calculates "2" there also, I believe correctly.  OR, I also notice a difference in arrival time between your two grids, for Patient 5... these discrepancies should explain any oddities.

[jeffwiswell]

In this case, it looks like the max is off

[JohnJPS]

I had added a few arbitrary rows (patients 7,8,9) just to observe what happened to the row counts (e.g. between a longer time frames vs. a self join).  Anyway, I've now modified the workflow to show the patients matching the range, plus a time at which all those patients would be present.  If you check carefully, Alteryx has the right answer.  :-)

Interestingly there are two ways for Patient 2's (and 3's) time frames to reach their max of 5.

[jeffwiswell]

Hi John--that looks great!  Do you mind helping with one more complexity?  I should've put it in the initial request but I was trying to simplify things as much as possible.  I'm having trouble because the real data also has date stamps and some patients come in one evening and aren't discharged until the next morning.  I will attach a file.  

[JohnJPS]

I've attached the same approach, this time using your dataset and which runs both my and @Claje's approach. I believe the output is the same however I must admit: mine is easily the s-l-o-w-e-r of the two: the self-join doesn't seem to be a bother at all, whereas mine results in quite a lot of unnecessary overhead.