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