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:

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:

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:

[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.

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.

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).

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.