8 Replies Latest reply on Mar 31, 2017 3:27 PM by Gerardo Varela

    I think I need to use a subquery, but not sure how to convert my temp table query

    Erik Carlstrom

      I'm using Tableau 10.2 and I have a query that gives me exactly what I need when I run in SQL Studio.  There are a couple temp tables in it, but for simplicity I'm focused on the first half of my query since I should be able to use the shelf to complete the rest. The query in question is the following:

      ---------------------------------------

      DECLARE

        @Start_Date DATETIME

        ,@End_Date DATETIME;

      ------------------------------------------

      SET @Start_Date = '2/1/2017';

      SET @End_Date = '3/1/2017';

      ------------------------------------------

      IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

      DROP TABLE #TempTable

       

      SELECT

          av.SLOT_BEGIN_TIME

         ,DATEADD(MINUTE , av.SLOT_LENGTH , av.SLOT_BEGIN_TIME) AS 'SLOT_END_TIME'

         ,av.PROV_ID

         ,CONVERT(DATE , av.SLOT_BEGIN_TIME) AS 'CONTACT_DATE'

      INTO

          #TempTable

      FROM

          dbo.AVAILABILITY_VIEW av

      WHERE

          av.SLOT_BEGIN_TIME >= @Start_Date

          AND av.SLOT_BEGIN_TIME < @End_Date;

      -----------------------------------------------------------------------------------------

      SELECT

          tt.PROV_ID

         ,tt.CONTACT_DATE

         ,MIN(tt.SLOT_BEGIN_TIME) AS 'First_Slot_Begin_Time'

         ,MAX(tt.SLOT_END_TIME) AS 'Last_Slot_End_Time'

         ,DATEDIFF(MINUTE , MIN(tt.SLOT_BEGIN_TIME) , MAX(tt.SLOT_END_TIME)) / 60.0 AS 'TimeElapsedHours'

      FROM

          #TempTable tt

      GROUP BY

          tt.PROV_ID

         ,tt.CONTACT_DATE;

       

      When I just try to build the query without the temp table along the following lines:

      SELECT DISTINCT

                      AVAILABILITY_VIEW.PROV_ID, SER1.PROV_NAME, CONVERT(DATE , AVAILABILITY_VIEW.SLOT_BEGIN_TIME) AS CONTACT_DATE,

                      MIN(AVAILABILITY_VIEW.SLOT_BEGIN_TIME) AS FIRST_SLOT_TIME, MAX(DATEADD(MINUTE , AVAILABILITY_VIEW.SLOT_LENGTH , AVAILABILITY_VIEW.SLOT_BEGIN_TIME)) AS LAST_SLOT_END,

                      CAST(DATEDIFF(MINUTE , MIN(AVAILABILITY_VIEW.SLOT_BEGIN_TIME), DATEADD(MINUTE , AVAILABILITY_VIEW.SLOT_LENGTH , MAX(AVAILABILITY_VIEW.SLOT_BEGIN_TIME))) AS NUMERIC(18 , 2))/ 60 AS HOURS_WORKED

       

      I end up with multiple results for each contact date.  Not unexpected since if I only use one SELECT statement in SQL Studio I get the same thing.  What I'm curious is how would I build this into a Query with a subquery so I only see one result per day?  One I have that I think I can do the rest with an IF statement, but for reference I would do the next sequence to convert the number of hours worked in a day to a shift value so here's a snippet of what I do in SQL Studio:

       

      SELECT

      .....

      ,CASE WHEN CAST(DATEDIFF(MINUTE , MIN(tt.SLOT_BEGIN_TIME) , DATEADD(MINUTE , 30 , MAX(tt.SLOT_END_TIME))) AS NUMERIC(18 , 2)) / 60 BETWEEN 1

                                                                                                                                              AND     5 THEN 1

               WHEN CAST(DATEDIFF(MINUTE , MIN(tt.SLOT_BEGIN_TIME) , DATEADD(MINUTE , 30 , MAX(tt.SLOT_END_TIME))) AS NUMERIC(18 , 2)) / 60 BETWEEN 5

                                                                                                                                              AND     10 THEN 2

               WHEN CAST(DATEDIFF(MINUTE , MIN(tt.SLOT_BEGIN_TIME) , DATEADD(MINUTE , 30 , MAX(tt.SLOT_END_TIME))) AS NUMERIC(18 , 2)) / 60 BETWEEN 10

                                                                                                                                              AND     16 THEN 3

               ELSE NULL

          END AS 'ShiftBlock'

       

      I know that using a stored procedure is something that is recommended, but unfortunately that is not a solution for me (nor is using a secondary server connection as a pass through).

        • 1. Re: I think I need to use a subquery, but not sure how to convert my temp table query
          Gerardo Varela

          Hi Erik,

          I would do all of this in Tableau. First query would be:

           

          SELECT

              av.SLOT_BEGIN_TIME

             ,DATEADD(MINUTE , av.SLOT_LENGTH , av.SLOT_BEGIN_TIME) AS 'SLOT_END_TIME'

             ,av.PROV_ID

             ,CONVERT(DATE , av.SLOT_BEGIN_TIME) AS 'CONTACT_DATE'

          FROM

            dbo.AVAILABILITY_VIEW av

           

           

          The WHERE clause  is just a regular range quick filter, set to context, for SLOT_BEGIN_TIME. If need be you can add this as datasource filter. The rest you can do in Tableau. Please be mindful of any typos and or calculation errors on my part.

           

          MIN(tt.SLOT_BEGIN_TIME) AS 'First_Slot_Begin_Time'

           

           

          You can translate that into a LOD expression. You might need to adjust to whatever time expression you are looking for:

           

          [First_Slot_Begin_Time] =

          {FIXED PROV_ID,CONTACT_DATE:MIN(SLOT_BEGIN_TIME)}

           

           

          MAX(tt.SLOT_END_TIME) AS 'Last_Slot_End_Time'

           

          [Last_Slot_End_Time]=

          {FIXED PROV_ID,CONTACT_DATE:MAX(SLOT_BEGIN_TIME)}

           

          [TimeElapsedHours]=

          DATEDIFF('hour',[First_Slot_Being_Time],[Last_Slot_End_Time])

           

           

          Your shifts would be an if statement in Tableau. Something to the sort of:

           

           

          IF [TimeElapsedHours] <= 5 AND [TimeElapsedHours] >= 1

              THEN "1"

          ELSEIF

              [TimeElapsedHours] >=5 AND [TimeElapsedHours] <= 10

             THEN "2"

          ELSEIF

              [TimeElapsedHours] >=1 AND [TimeElapsedHours] <= 16 //You sure it's 16 not 15?

             THEN "3"

          ELSE

              NULL

          END

           

          I hope this helps!

          Gerardo

          • 2. Re: I think I need to use a subquery, but not sure how to convert my temp table query
            Erik Carlstrom

            Thanks for the response Gerardo Varela.  To clarify for the first part you're saying replacing the following:

            SELECT DISTINCT

                            AVAILABILITY_VIEW.PROV_ID, SER1.PROV_NAME, CONVERT(DATE , AVAILABILITY_VIEW.SLOT_BEGIN_TIME) AS CONTACT_DATE,

                            MIN(AVAILABILITY_VIEW.SLOT_BEGIN_TIME) AS FIRST_SLOT_TIME, MAX(DATEADD(MINUTE , AVAILABILITY_VIEW.SLOT_LENGTH , AVAILABILITY_VIEW.SLOT_BEGIN_TIME)) AS LAST_SLOT_END,

                            CAST(DATEDIFF(MINUTE , MIN(AVAILABILITY_VIEW.SLOT_BEGIN_TIME), DATEADD(MINUTE , AVAILABILITY_VIEW.SLOT_LENGTH , MAX(AVAILABILITY_VIEW.SLOT_BEGIN_TIME))) AS NUMERIC(18 , 2))/ 60 AS HOURS_WORKED

             

            With:

            SELECT DISTINCT

                            AVAILABILITY_VIEW.PROV_ID, SER1.PROV_NAME, CONVERT(DATE , AVAILABILITY_VIEW.SLOT_BEGIN_TIME) AS CONTACT_DATE,

            [First_Slot_Begin_Time] =

            {FIXED PROV_ID,CONTACT_DATE:MIN(SLOT_BEGIN_TIME)},

            [Last_Slot_End_Time]=

            {FIXED PROV_ID,CONTACT_DATE:MAX(SLOT_BEGIN_TIME)},

            [TimeElapsedHours]=

            DATEDIFF('hour',[First_Slot_Being_Time],[Last_Slot_End_Time])

             

             

            For the shifts then it would be:

            IF [TimeElapsedHours] <= 5 AND [TimeElapsedHours] >= 1

                THEN "1"

            ELSEIF

                [TimeElapsedHours] >=5 AND [TimeElapsedHours] <= 10

               THEN "2"

            ELSEIF

                [TimeElapsedHours] >=10 AND [TimeElapsedHours] <= 16 //I use 16 to err on the high side, highest I've seen was 14.66667

               THEN "3"

            ELSE

                NULL

            END

             

            I assume that I use that in the shelf then, correct? 

             

            I'll share the rest of the custom query I have been using in Tableau as well.  End result I'm hoping is a table with this info and then add in the data from a separate query so I can show this data, the other data, and then the ratio between the two. 

             

            SELECT DISTINCT

                            AVAILABILITY_VIEW.PROV_ID, SER1.PROV_NAME, CONVERT(DATE , AVAILABILITY_VIEW.SLOT_BEGIN_TIME) AS CONTACT_DATE,

                            MIN(AVAILABILITY_VIEW.SLOT_BEGIN_TIME) AS FIRST_SLOT_TIME, MAX(DATEADD(MINUTE , AVAILABILITY_VIEW.SLOT_LENGTH , AVAILABILITY_VIEW.SLOT_BEGIN_TIME)) AS LAST_SLOT_END,

                            CAST(DATEDIFF(MINUTE , MIN(AVAILABILITY_VIEW.SLOT_BEGIN_TIME), DATEADD(MINUTE , AVAILABILITY_VIEW.SLOT_LENGTH , MAX(AVAILABILITY_VIEW.SLOT_BEGIN_TIME))) AS NUMERIC(18 , 2))/ 60 AS HOURS_WORKED

             

             

            FROM

                            [Clarity].[dbo].[AVAILABILITY_VIEW]

                            LEFT JOIN Clarity.dbo.CLARITY_SER_VIEW SER1 ON AVAILABILITY_VIEW.PROV_ID=SER1.PROV_ID

             

             

            WHERE

                            AVAILABILITY_VIEW.SLOT_BEGIN_TIME>=GETDATE()-1098 AND

                            AVAILABILITY_VIEW.SLOT_BEGIN_TIME<=GETDATE() AND

                            --AVAILABILITY_VIEW.PROV_ID=570001 AND

                            AVAILABILITY_VIEW.PROV_ID NOT IN ('5700001','5700002','5700003','5700004',

                            '5700006','5700007','5700008','5700009','5700010','5700146','57027','57031')

             

             

            GROUP BY

                            AVAILABILITY_VIEW.PROV_ID,

                            SER1.PROV_NAME,

                            AVAILABILITY_VIEW.SLOT_BEGIN_TIME,

                            AVAILABILITY_VIEW.SLOT_LENGTH

             

             

            Erik

            • 3. Re: I think I need to use a subquery, but not sure how to convert my temp table query
              Gerardo Varela

              Hi Erik,

                   First off let me apologize for not explaining myself completely Let's take a step back. Your first query is:

              SELECT

                  av.SLOT_BEGIN_TIME

                 ,DATEADD(MINUTE , av.SLOT_LENGTH , av.SLOT_BEGIN_TIME) AS 'SLOT_END_TIME'

                 ,av.PROV_ID

                 ,CONVERT(DATE , av.SLOT_BEGIN_TIME) AS 'CONTACT_DATE'

              INTO

                  #TempTable

              FROM

                  dbo.AVAILABILITY_VIEW av

              WHERE

                  av.SLOT_BEGIN_TIME >= @Start_Date

                  AND av.SLOT_BEGIN_TIME < @End_Date; 

               

              You really don't want to use custom sql whenever possible.  So the above I would translate into a simple select statement within Tableau's connection wizard. It would look like the below.

               

              SELECT

                  av.SLOT_BEGIN_TIME

                 ,DATEADD(MINUTE , av.SLOT_LENGTH , av.SLOT_BEGIN_TIME) AS 'SLOT_END_TIME'

                 ,av.PROV_ID

                 ,CONVERT(DATE , av.SLOT_BEGIN_TIME) AS 'CONTACT_DATE'

              FROM

                dbo.AVAILABILITY_VIEW av

               

              Your second sql statement can be replaced with calculated fields in Tableau. The brackets indicate the name of the calculated field that I would create in Tableau followed by the formula that replaces the sql statement.

               

               

              [First_Slot_Begin_Time] =

              {FIXED PROV_ID,CONTACT_DATE:MIN(SLOT_BEGIN_TIME)}

               

              That should replicate your sql statement:

               

              MIN(tt.SLOT_BEGIN_TIME) AS 'First_Slot_Begin_Time'

               

              The same can be said for the following calculated field in Tableu:

               

              [Last_Slot_End_Time]=

              {FIXED PROV_ID,CONTACT_DATE:MAX(SLOT_BEGIN_TIME)}

               

              This should replace:

               

              MAX(tt.SLOT_END_TIME) AS 'Last_Slot_End_Time'

               

              Now that we have the above sql statements translated in Tableau we can just take the difference of the calculated fields from the following Tableau calculated field.

               

              [TimeElapsedHours]=

              DATEDIFF('hour',[First_Slot_Being_Time],[Last_Slot_End_Time])

               

              Then I would use the above to bin your shift blocks with a new calculated field in Tableau.

               

              [Shiftblock]=

               

              IF [TimeElapsedHours] <= 5 AND [TimeElapsedHours] >= 1

                  THEN "1"

              ELSEIF

                  [TimeElapsedHours] >=5 AND [TimeElapsedHours] <= 10

                 THEN "2"

              ELSEIF

                  [TimeElapsedHours] >=10 AND [TimeElapsedHours] <= 16 //I use 16 to err on the high side, highest I've seen was 14.66667

                 THEN "3"

              ELSE

                  NULL

              END

               

              I haven't had chance to look at your third sql statement you posted.  Let's see if you can recreate the the first part your question in Tableau before jumping into the next part.

               

              If this does make sense let me know!

               

              Regards,

              Gerardo

              • 4. Re: I think I need to use a subquery, but not sure how to convert my temp table query
                Gerardo Varela

                Here is why you don't want to use custom sql.  Tableau essentially does a sub-query for your custom sql.

                 

                SELECT *

                FROM

                (

                Your custom sql

                )

                 

                 

                Then as you start to drag and drop pills  Tableau will wrap its sql around the above.

                 

                Regards,

                Gerardo

                • 5. Re: I think I need to use a subquery, but not sure how to convert my temp table query
                  Erik Carlstrom

                  Thanks Gerardo Varela.  That gets me closer.  Ignore the third statement as it was just a more complete paste of what I had before.

                   

                  So when I use TimeElapsedHours in the chart I see what look like correct numbers as it sums the total hours worked.  When I add the Shiftblock calculated field in though I end up with colored blocks.  When I create the calculated field it puts it into the dimensions section.  I tried dragging down to the measures, but when I add to the chart it leaves the "Abc" entries and when I hover over I see values that don't make sense from an accuracy standpoint.

                   

                  Since the workbook is an extract and doesn't contain patient info I'll attach the workbook so you can see it.  I think once I can get the results to show up in the table as a number correctly that I can then move on to what I want to next which is adding in a new datasource (I assume that is what I would want to do to use data from two separate queries in the same graph).

                  • 6. Re: I think I need to use a subquery, but not sure how to convert my temp table query
                    Gerardo Varela

                    Hi Erik,

                         I would delete the workbook you posted because it does contain some meta data that I believe is confidential.  If you change the calculation ShiftBlock to the following it should make it a measure.

                     

                    [Shiftblock]=

                     

                    IF [TimeElapsedHours] <= 5 AND [TimeElapsedHours] >= 1

                        THEN 1

                    ELSEIF

                        [TimeElapsedHours] >=5 AND [TimeElapsedHours] <= 10

                       THEN 2

                    ELSEIF

                        [TimeElapsedHours] >=10 AND [TimeElapsedHours] <= 16 //I use 16 to err on the high side, highest I've seen was 14.66667

                       THEN 3

                    ELSE

                        NULL

                    END

                     

                     

                     

                    Regards,

                    Gerardo

                    • 7. Re: I think I need to use a subquery, but not sure how to convert my temp table query
                      Erik Carlstrom

                      Thank you Gerardo Varela.  That fixed things and I deleted the workbook as you're correct.  I'll play around with adding in a secondary source and if I have trouble I'll create a new post.  Much appreciated. 

                      • 8. Re: I think I need to use a subquery, but not sure how to convert my temp table query
                        Gerardo Varela

                        You're welcome and have a wonderful weekend.

                         

                        Regards,

                        Gerardo