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

[jeffwiswell]

Thanks John!  This is really close but still found an instance where it is not counting.  I added some additional rows of data to test it (attached).

For example, patient 4 would be in the same department at some point with patient 3, 5, 6, 7, and 8.  It seems to be missing the patient above, which is patient 3.  It also misses patient 8.  It should have a max of 6 instead of 4.

Any ideas?

[JohnJPS · Accepted solution]

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.

[jeffwiswell]

Thanks!  You make a really good point--I've been trying to wrap my head around it but I think that makes the most sense.