3 Replies Latest reply on Sep 11, 2016 7:28 AM by Tom W

    How translating this SQL query to tableau?

    Aleks Roth

      3.JPG

       

      I have a SQL inquiry which shows time activity of each account. Database Microsoft SQL Server works with using microsoft server 2008.

      Help me please with translating 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

        • 1. Re: How translating this SQL query to tableau?
          Tom W

          What exactly do you mean 'translate' this to Tableau? Do you intend to connect to this stored procedure directly exactly as it is? Or are you trying to connect to the individual tables in the connection window then impose filters in Tableau to replicate the stored proc? Or take the SQL out of the stored procedure and put it into a 'custom sql' within Tableau?

          • 2. Re: How translating this SQL query to tableau?
            Aleks Roth

            I would like to use this SQL query in tableau.  This query works in MS SQL Server 2008. But when I connecting to MS SQL Server and use the query in tableau, request does not work. There is parameters Parameters.Date1 and Parameters.Date2 on my worksheet in tableau. I would like to use Parameters.Date1 and Parameters.Date2 instead of @time. How should I change SQL query in order to it works in tableau?

            • 3. Re: How translating this SQL query to tableau?
              Tom W

              Take a look at the help page on custom sql, there is a specific section on using parameters.

               

              Connect to a Custom SQL Query

               

              You will need to remove the DECLARE @time from your SQL and only have the select in the custom sql dialog.
              You then need to replace each instance of @time in your SQL to use the parameters you've created. That should be pretty straight forward in the where clause, it should look something like WHERE Start >= <parameters.date1> and Start <= <Parameters.Date2>

               

              I don't know how you would change this reference though; cast(DATEADD(SECOND, sum(datediff(DAY, @time,[Start])),@time) as date)'Date'

              Your procedure right now relies on ONE date field so this works fine. However if you move to an approach with two date parameters, I don't know which you would use here. It feels like you should probably use the [Time] field in the datasource, but I don't know enough about your schema or data to advise.