i'd recommend you start by defining what data you need to be able to make such a calculation, and if it's not available, calculate it in the script;
at first siight, you need to have a field for each procedure that contains the end of the previous procedure in the same room on the same day
you should be able to generate that field in the script using some "advanced" manipulations of your tables (order them by time, compare previous rows, conditionnaly calculate the end of the previous procedure for a certain procedure).
when finished, i'm not even convinced you'll need set analysis...
This data is auto-populated from every night at midnight, so I'm not sure the table function would work.
The fields I need are Operating Room Name, Surgery Date, Patient In, Patient Out. The previous patient Patient Out to the next Patient In is where I'm stuck. The table function would work, but I'm not sure how that would work with the source system field.
don't expect this to be automatic... this is about bringing "intelligence" to data :
i'd think you'd have 3 steps (or more :-) ) :
1) create temporary table containing data from source system temp1: Load *; Sql select * from ***; etc.
2) create a table in which you do the "special" calculations:
- it needs to be sorted in order to allow calculations (order by)
- then use previous() function in combination with if() expressions to define your logic
if(previous(OpRoom)= OpRoom and previous(Date)= Date, previous(endTime)) as PreviousOpEndTime
order by OpRoom asc, Date asc;
3) additionnal creation of fields etc; dropping temp tables; etc
StartTime - PreviousOpEndTime as DelayBetweenPreviousAndStartOp
resident tempTable 2;