I'm not clear on what your goal is exactly? What are you trying to display, sort by, group, by, and/or filter by?
Our OR Dept is currently creating schedule time blocks and assigning these to Surgeons and Surgeon Groups. They created the schedule so that it has 5 complete weeks with time blocks for the hours the OR is officially open (say 7:30AM-3:30PM, M-F). With this scheduling template, they create the monthly time blocks based on the calendar month. For example, Dec1, 2016 fell on a Thursday, so the time blocks from that day are those for Week1-Thursday from the time block template. Monday, Dec5th would use the Week2-Monday time blocks. For each month, they would realign for the first business day of the month. Jan1, 2016 was a Sunday, so it would be Week1-Sunday schedule.
I was able to make an event record for each schedule block that has the following fields and carried this through calendar year 2017:
This is currently in excel. Lets refer to this as the Block Times. Also, these block times can be altered at anytime for when there is a new Surgeon or group, so having a way for the department to update the scource data is ideal.
We use a SQL Server platform for our Actuals data that updates Tableau Server Data Source extract daily. Each record in the this field is for a single surgical event and has timestamp data for Anesthesia Time Start/Stop. Case Cancellation Datetime, OR Setup Time Start/Stop, OR Time Start/Stop, PACU time Start/Stop, Proceduree Date, Scheduled Case Start Time, Stop Time, Surgery Time Start/Stop and Case Sign Off Datetime. All of these were defined after extended discussion with the OR Team and these are used to calculate measures such as Anesthesia Time Duration, OR Setup Time Duration, OR Time Duration, PACU Time Duration, Scheduled Case Time Duration, Signed Off delay time, and Surgery Time Duration. Other calculated fields define Actual Case Time, Add On %, Case Delay Minutes, Scheduled Case Time, etc…
What my department (Decision Support) has been asked to do is to visually display the following:
- For each day, show the Block Times vs Scheduled Case Times vs the Actual Case Times (Setup/Anesthesia/Surgery/etc) by OR Room.
- For each month, summarize the data by the various minute classifications as well as other fields in the data such as Patient Type, Gender, Age, Zip Code, Procedure Type, etc
My problem is linking the Block times into the Sql data (Tableau Extracts updated daily) and then creating the visuals for this data.
This is what I was visualizing for the Daily View:
With this background, assistance/advice on how to set up the Block Times data so it can be used with the actual event records is greatly appreciated. Also, any recommendations on the best way to
visualize this data is appreciated.
If anyone would like to discuss offline, I can be reached at DGordon@fmh.org. Thank you in advance for any assistance!
Sorry for the late reply…
The simplest suggestion I have is doing a union of the two sources, rather than some other sort of join. The reason why is that you want to plot the blocks inline with the times in the case data. Also, for your display the case data should be in a ‘tall’ structure.
So the data would look something like this:
[Source] [Category] [Start] [Stop] [OR Room] [...Other Fields…]
Cases Setup Time
Cases Anesthesia Time
Cases Surgery Time
The filters can get a little tricky because the case data has patient type, gender, etc. but the block information does not.
So a more complex data source would do the union *and* for the block info include a join to the case info so that every block has rows for all the cases in that block. This join can get pretty complicated to write because you’d need to figure out which block each case falls into (if it falls into a block at all).
Hope this helps!