6 Replies Latest reply on Oct 12, 2015 11:50 PM by Alexander Mou

    Employees/Patients Time Issue

    Mason Jones

      Hello all!


      I have read through most of the open close time FAQ forum, but I haven't seen anything that quite solves my problem.  I have a data table that for each row displays an employee's clock in/clock out time and dept name (Pre-Op, OR, Recovery).  I have another table that displays a patient's Pre-Op time in/out, OR time in/out, and Recovery time In/Out.  The date range is YTD.



      I need to plot, at either 5 or 15 minute intervals (whichever is easier), the average number of patients in the OR and the average number of OR employees on the clock in the same graph.  The average though would be the total number of employees clocked in at a given time interval/Number of days the facility is open and the number of patients in the OR at a given time interval/Number of days the facility is open.  A distinct count of date would work, but tableau won't allow you to use that calculation across blended data sources.  I was able to graph employees and patients on separate graphs using calculated fields for each time interval, but there's just no way to combine them.


      The excel file includes a worksheet that shows the desired finished product.  Also, I have multiple facilities, so I would need to filter on facility id.


      Test Data

      I attached an excel file with test data that represents what I'm using.  Also, I'm pulling the employees and patient data directly from a database. I just mocked up the excel file, so I could publish the question.  The "Created Time" worksheet is just my attempt at trying some of the methods I've seen on the forum.


      Sorry to make this harder, but I know almost nothing about writing SQL code.  So... any explanation that includes writing custom SQL code will need to be extremely detailed.  I would prefer not to use it altogether if possible.


      Any help here would be greatly appreciated!



        • 1. Re: Employees/Patients Time Issue



          You've probably already seen this, but the following link is a comprehensive discussion of the various approaches:

          Vizible Difference: Taking Stock with Start and End Dates

          I 'm likely wrong, but I fear that the NonSQL Approach 2 will not work for you because your data is on different sheets.


          But, I think your Excel sheet is already set up for an SQL version of Approach1 to work.


          The CustomSQL will accomplish merging the data from separate sheets and giving you a common time axis.

          Here is a preliminary sketch of one version of the CustomSQL:


          SELECT  "Patient" as [Type],                   // you want patient and employee data merged, so this denotes the type

                  p.[Patient Key] as [ID],                      //  pull in the ID from the patient sheet

                  "OR" as [Department],                      //  this is an example for the OR data

                  p.[Date of Service] as [Date],            //  pull in the date

                  c.[Time]                                             //  pull in the time from the Created Time worksheet

          FROM [Patients$] p, [Created Time$] c    // from which sheet, for convenience labelled with one letter

          WHERE c.[Time]>=p.[OR Start]                // pulls in all the 15 min times when the patient is in the OR

          AND c.[Time]<p.[OR End]                      

          UNION                                                      // merge with the employee version of the same as the above

          SELECT  "Employee" as [Type],              // has the same format as above: [Type], [ID], ...

                  e.[Emp Name] as [ID],

                  "OR" as [Department],

                  e.[WorkDate] as [Date],


          FROM [Employees$] e, [Created Time$] c

          WHERE e.[Dept Name]="Operating Room"

          AND c.[Time]>=e.[In Punch]

          AND c.[Time]<e.[Out Punch]

          • 2. Re: Employees/Patients Time Issue
            Mason Jones

            I really appreciate your help on this.  I think I can only write a SQL code that merges patients and employees because those are coming from the same database though.  The "Created Time" in the excel file would be a separate data source.  Is it possible to write SQL code connecting databases to excel files?

            • 3. Re: Employees/Patients Time Issue
              Alexander Mou



              You can use the second method by creating a simple scaffolding table first. No SQL code is necessary.

              • 4. Re: Employees/Patients Time Issue
                Mason Jones

                Hi Alexander,


                Thanks for the the response!  I tried the scaffolding approach, but my chart just showed 0 employees throughout the day.  Also, I would need to filter on the department name and the facility ID, so I'm not exactly sure what the scaffold table would look like.  Thoughts?

                • 5. Re: Employees/Patients Time Issue
                  Alexander Mou

                  In this case, you will need a multi dimensional scaffolding since you have more dimensions to filter by.

                  Just made up an example where I added two more dimensions for filtering. It is based on the aforementioned article.



                  Attached is an excel file that contains the 3 dimensions, each in single column on a separate sheet.

                  Used custom sql to generate a 3-dimensional scaffolding:

                  • select * from [Date$],[Customer Segment$],[Product Category$]

                  This generates a 24-row and 3-column scaffolding table.


                  Make sure that all the secondary sources are blended with the MultiD scaffolding as primary in all three dimensions.

                  Let me know if issues. Attach screenshots if possible.

                  • 6. Re: Employees/Patients Time Issue
                    Alexander Mou

                    In the scaffolding table, each dimension must include all the members of the dimension. If it is a date, it needs to include the start date and the end date for the entire date range of interest. Make sure to turn on 'Show Missing Values" on the Date pill. This will show all the dates in between, a technique called data densification.


                    In your case, you need to include all the facility ids and all the department names.