3 Replies Latest reply on Apr 5, 2017 7:29 PM by Jonathan Drummey

    Rotating Schedule based on Calendar month

    Dan Gordon

      Hello all,

      Have another situation that I am struggling with.   The OR Department uses a rotating 5 week block schedule to book an OR Room based on the monthly calendar.  If the 1st of the month falls on a Thursday, then Week1 - Thursday would be the schedule for that day.  The following Monday, the 5th, would be Week 2 - Monday.  (See below calendar).  

         Schedule Example.png

      My question is how do I link this schedule to a time master table in Tableau?  I am admittedly a novice when it comes to SQL, but I am assuming (dangerous I know) that this should be handled with SQL prior to being loaded into Tableau.  I am using SQL Server as data source for most Tableau data sources.  However this block calendar is currently in Excel but easily formatted for Access.  Would have to format to load into SQL, but could easily accomplish.  Is this a situation where I create an "event" for Tableau?  Can someone please point me to where this may have already been discussed (I searched but don't see this situation addressed)?  I appreciate in advance any assistance.

       

      (I can't load the excel file as it has doctor information.  I could mock something up if you need to see what I am working with)

       

      Dan Gordon

      Decision Support Project Manager

      Frederick Memorial Hospital

      DGordon@fmh.org

       

        • 1. Re: Rotating Schedule based on Calendar month
          Jonathan Drummey

          Hi Dan,

           

          I'm not clear on what your goal is exactly? What are you trying to display, sort by, group, by, and/or filter by?

           

          Jonathan

          • 2. Re: Rotating Schedule based on Calendar month
            Dan Gordon

            Hello Jonathan,

            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:

            OR Block Schedule.png


            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:

            1. For each day, show the Block Times vs Scheduled Case Times vs the Actual Case Times (Setup/Anesthesia/Surgery/etc) by OR Room.
            2. 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:

            Daily OR Room View.png

            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!

            • 3. Re: Rotating Schedule based on Calendar month
              Jonathan Drummey

              Hi Dan,

               

              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…]

              Blocks Block

              Cases Scheduled

              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!

               

              Jonathan