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 Mar 30, 2017 2:40 PM

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 Mar 30, 2017 4:36 PM (in response to Erik Carlstrom)

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
Like Show 0 Likes(0)

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

Erik Carlstrom Mar 30, 2017 5:42 PM (in response to Gerardo Varela)

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
Like Show 0 Likes(0)

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

Gerardo Varela Mar 31, 2017 4:49 AM (in response to Erik Carlstrom)

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
Like Show 0 Likes(0)

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

Gerardo Varela Mar 31, 2017 6:41 AM (in response to 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
Like Show 1 Likes(1)

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

Erik Carlstrom Mar 31, 2017 10:48 AM (in response to Gerardo Varela)

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).
Like Show 0 Likes(0)

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

Gerardo Varela Mar 31, 2017 10:41 AM (in response to Erik Carlstrom)

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
Like Show 0 Likes(0)

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

Erik Carlstrom Mar 31, 2017 10:51 AM (in response to Gerardo Varela)

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.
Like Show 0 Likes(0)

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

Gerardo Varela Mar 31, 2017 3:27 PM (in response to Erik Carlstrom)

You're welcome and have a wonderful weekend.

Regards,
Gerardo
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community Forums

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

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

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

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

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

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

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

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

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

Actions

More Like This

Incoming Links