at TC18 in October there was session on data prep that had an example that is exactly your problem
the first link is a you tube of the session
this is the accompanying pdf
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
I gave it a go with your example data and thought I got somewhere with it initially with Physician A, however I haven't see this type of resulting behavior before for Physician B with a Table Calc that appears to be partitioning correctly, so perhaps someone else can weigh-in.
2018.3 workbook attached. Thx, Don
First you need to create a self join to get the desired output. For case no you have to join on next case number i.e case number +1.
Then you need to find the time difference in minutes between end time of 1st case number and start time of next case no for each hospital, physician, and operating room.
Then average time can be easily calculated with Lod.
Hope this helps.
Book10_v10.5.twbx 16.8 KB