CODEWITHSUNDEEP
sqlsql-server-2008tableau-api
Serg
Serg

Reputation: 93

How to translate this SQL query to tableau?

enter image description here

I have a SQL query which shows time activity of each account. Database is Microsoft SQL Server on Windows Server 2008.

Help me please to translate this query to tableau with using parameters Parameters.Date1 and Parameters.Date2 instead of @time.

The result of the query:

USER,Date,Total time

USER1,2016-09-22,07:00:00.0000000  
USER2,2016-09-22,08:00:00.0000000    
USER3,2016-09-22,05:00:00.0000000

SQL query:

DECLARE @time datetime
set @time = '08.09.2016'

SELECT 
    [User],
    CAST(DATEADD(SECOND, sum(datediff(DAY, @time, [Start])), @time) AS date) 'Date',
    CAST(DATEADD(SECOND, sum(datediff(SECOND, '00:00:00',[Period])), '00:00:00') AS time) 'Total time'
FROM 
    [User].[dbo].[UserAction]
WHERE 
    [Start] >= @time+'00:00:00' and [Start] <= @time+'23:59:59'
GROUP BY 
    [USER]

input data to build the query:

USER, Start,End,Period

USER1,2016-09-22 09:00:00.000,2016-09-22 12:00:00.000,03:00:00
USER1,2016-09-22 12:00:00.000,2016-09-22 13:00:00.000,01:00:00
USER1,2016-09-22,13:00:00.000,2016-09-22 16:00:00.000,03:00:00
USER2,2016-09-22,09:00:00.000,2016-09-22 13:00:00.000,04:00:00
USER2,2016-09-22,13:00:00.000,2016-09-22 17:00:00.000,04:00:00
USER3,2016-09-22,09:00:00.000,2016-09-22 10:00:00.000,01:00:00
USER3,2016-09-22,10:00:00.000,2016-09-22 12:00:00.000,02:00:00
USER3,2016-09-22,12:00:00.000,2016-09-22 14:00:00.000,02:00:00

Upvotes: 0

Views: 1066

Answers (2)

Alex Blakemore
Alex Blakemore

Reputation: 11896

You don't need custom SQL for this. Keep it simple. Connect Tableau directly to your UserAction table.

You can either: Put Day(Start) on the filter shelf, Make sure it is a continuous Date truncated to the Day. Show the filter and set the filter to let you pick a single value at a time - I would choose a slider UI.

Or write a calculated field to put on the filters shelf that references a parameter such as day(Start) = day(Date1)

Put User on one shelf, such as rows, and Sum(Period) on another such as columns. That should do it unless Tableau has trouble interpreting your Period field datatype. If so, try changing the datatype to Number inside Tableau to see if it converts durations to numbers automatically, if not you may need to write a calculated field for the conversion.

Upvotes: 0

Thomas M Porter II
Thomas M Porter II

Reputation: 86

I don't have enough imaginary stack overflow points yet to make a comment instead of an answer, but I would agree with Gordon Linoff.

A table valued function in sql can be used directly in a Tableau data source, and it's treated just like a table.

Note I did not test the below, but here is what the equivalent function might look like:

CREATE FUNCTION dbo.MyFuntion (@time datetime)  
RETURNS TABLE  
AS  
RETURN   
(
SELECT 
  [User]
  ,cast(DATEADD(SECOND, sum(datediff(DAY, @time,[Start])),@time) as date)'Date'
  ,cast(DATEADD(SECOND, sum(datediff(SECOND, '00:00:00',[Period])),'00:00:00') as time)'Total time'
FROM 
  [User].[dbo].[UserAction]
WHERE 
  [Start] >= @time+'00:00:00' and [Start] <= @time+'23:59:59'
GROUP BY [USER]
);

Tableau 9 (haven't tried 10) seems to discourage custom SQL (it warns anyone that opens your workbook) and stored procedures (slow vs. same sql in a function).

Alternatively, adding the pure dbo.UserAction table to a data source and making calculated fields for the second two columns might work: Tableau Documentation. It seems to have all the functions needed to manipulate dates. However, there may be some crazy limitation associated with parameters that might limit it, honestly can't remember off the top of my head.

Upvotes: 1

Related Questions