How can I run my custom function and query in a loop for different time frames?

Question

I am writing a function to calculate the total number of seconds a user was online at my website. Afterwards, I convert the number of seconds to hh:mm:ss:

select * into #temp from [MyFunction](timestamp1, timestamp2);

select u.Name, 
       convert(varchar(8), t.Seconds / 3600) + ':'
             + right('0', convert(varchar(2) t.Seconds % 3600/60), 2) + ':'
             + right('0', convert(varchar(2) t.Seconds % 60), 2)
    as [Total Time]
from #temp t left join Users u
    on t.UserID = u.UserID;

Where an example timestamp is 2016-04-01 00:00:00.000. What I want now, is to see total time spent on my website, not on 1 range, but a sequence of ranges, for instance:

2016-01-01 to 2016-01-15
2016-01-16 to 2016-01-31
2016-02-01 to 2016-02-15

Is it possible to put my code in a dynamic query to calculate all of these ranges by running the same code every time?

The output of my code above is:

Name    [Total Time]
--------------------
Anton   6:34:55
Bert    5:22:14

What I would like is an output such as

Name    [Period_1] [Period_2] [Period_3] [Period_4]
---------------------------------------------------
Anton   6:34:55    5:00:22    null       10:44:32
Bert    5:22:14    null       null        9:22:53

So each range, or loop over the code, should be a column.

I believe pivot() will help me here, but any help kickstarting me with the dynamic SQL (or any better solution) would be greatly appreciated.

hmmm -- would you be happy with a comma separated list -- that is much easier and you won't need dynamic sql. — Hogan, Apr 01 '16 at 19:05
This sounds like a dynamic pivot to me. It has been asked and answered hundreds of times around here. — Sean Lange, Apr 01 '16 at 19:13
Creating columns dynamically based on the data is not something SQL is good at. Your task will be a lot more simple if you return data in rows and make the pivot outside the database — James Z, Apr 01 '16 at 19:20
[JamesZ] - I have to do this in SQL because I have to run it in a dashboard tool for management that only supports queries [Tom H] ranges are defined as 2 timestamps (dateBegin and dateEnd) [Sean Lange] correct, dynamic pivot. The pivot part I can do; i have read the other posts but the dynamic query is something i am struggling with. — Pr0no, Apr 01 '16 at 19:59
I will query another table for distinct dates. Then, every two dates are input for this SQL. So: date1 (=begin) date2 (=end), date3 (=begin) date4 (=end) etc. — Pr0no, Apr 04 '16 at 06:43
Please attach sample data and desired output based on that data. — Kamil Gosciminski, Apr 04 '16 at 20:32
might be crone job do the job. if you can login to your website cpanel you can try crone job to run the script. — asela daskon, Apr 05 '16 at 03:00
@Pr0no, The result of your function is not enough to provide an answer. Provide the DDL for the table with the user online information. The dates from that table are needed to bucket the time spent online into date ranges. — Dan Guzman, Apr 05 '16 at 11:36

score 0 · Answer 1 · edited May 23 '17 at 10:28

Wrap your current code into a procedure with parameters, something like:

CREATE PROCEUDRE dbo.CalcTime
  @Period       varchar(100)  --  Name of the period
 ,@PeriodStart  datetime      --  Period starts
 ,@PeriodEnd    datetime      --  Period ends

and using appropriate datatypes.

Next, create a second procedure. Within this one, define another temporary table, like

CREATE TABLE #Results
 (
   Name       varchar(100)  not null  --  Or however long it might get
  ,Period     varchar(100)  not null  --  Ditto
  ,TotalTime  int           null      --  *
 )

Loop over every period you wish to define data for. For each period, call the "CalcTime" stored procedure, and dump the results into the temp table. Two ways to do this, use

INSERT #Results
 execute dbo.CalcTime  'Period', 'Jan 1, 2016', 'Jan 15, 2016'

or, having defined the temp table in the calling procedure, you can reference it in the called procedure in a standard INSERT... SELECT... statement.

Also within the loop, build a comma-delimited string that lists all your period labels, e.g.

SET @AllPeriodLabels = isnull(@AllPeriodLabels + ',', '') + @ThisPeriodLabel

or,

SET @AllPeriodLabels = isnull(@AllPeriodLabels + ',', '') + '[' + @ThisPeriodLabel + ']'  --  **

Use this to build the dynamic SQL pivot statement against the temp table, and you’re done. As mentioned in the comments, there are any number of SO posts on how to do that, and here are links to two: The first discusses building a dynamic pivot statement, and the second uses similar tactics for an unpivot statement.

* Avoid embedded spaces in object names, they will only give you pain.

** Ok, Sometimes you have to do it.

With more sample data, I--or someone else--could provide a more detailed answer. — Philip Kelley, Apr 04 '16 at 20:58

score 0 · Answer 2 · answered Apr 06 '16 at 22:30

Two pseudo tables:

persons:
  personId int
  lastname nvarchar(50)

visits:
  personid int
  created datetime
  duration int -- (store things like duration in seconds)

First make a list of the columns, here I used a created column and converted it to a month period. So the result is something like: [201501],[201502],[201503],....

declare     @cols nvarchar(max)
set         @cols = STUFF((select ',' + quotename(convert(VARCHAR(6), created, 112))
                        from        visits
                        group by    convert(VARCHAR(6), created, 112)
                        order by    convert(VARCHAR(6), created, 112)
                    for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')

I need dynamic SQL to fill in the variable number of COLs, I suggest you start with NON dynamic SQL, make it dynamic should be the last step.

declare     @sql nvarchar(max)
set         @sql = N'
                    select      *
                    -- lazy create a temp so you don't have to bother about the column definitions
                    -- into #temp 
                    from (
                        select      p.lastname, convert(VARCHAR(6), created, 112) as period
                        -- this is optional to get a Grand Row total
                        -- ,(select sum(duration) from visits v where v.personId = p.personId) as total
                        from        visits v
                                    inner join persons p on v.personId = p.personId
                    ) src
                    pivot (
                        sum(duration) for period in (' + @cols + ')
                    ) pvt;
            '

Well you can print this for verification or run it ...

exec sp_executesql @sql

You can make a twist by dumping the result in a temp table (created on the fly). That creates the opportunity to add extra columns for output, like an organization etc. etc..

alter table #temp add organization nvarchar(100)

Good luck !

score 0 · Answer 3 · answered Apr 08 '16 at 08:46

Here is a working test code. Adapt it as you see fit.

Setup:

-- create test tables
CREATE TABLE Users 
  ( 
     UserId   INT, 
     UserName NVARCHAR(max) 
  ) 

CREATE TABLE Access 
  ( 
     UserId    INT, 
     StartTime DATETIME2, 
     EndTime   DATETIME2 
  ) 

CREATE TABLE Periods 
  ( 
     NAME      NVARCHAR(max), 
     StartTime DATETIME2, 
     EndTime   DATETIME2 
  ) 

go 

-- function to format the time
CREATE FUNCTION ToTime(@SECONDS BIGINT) 
returns NVARCHAR(max) 
AS 
  BEGIN 
      RETURN CONVERT(VARCHAR(8), @SECONDS / 3600) + ':' 
             + RIGHT('00'+CONVERT(VARCHAR(2), @SECONDS % 3600/60), 2) 
             + ':' 
             + RIGHT('00'+CONVERT(VARCHAR(2), @SECONDS % 60), 2) 
  END 

go 

-- populate values
INSERT INTO Users 
VALUES     (1, 'Anton'), 
           (2,'Bert') 

DECLARE @I INT=100 
DECLARE @D1 DATETIME2 
DECLARE @D2 DATETIME2 

WHILE ( @I > 0 ) 
  BEGIN 
      SET @D1=Dateadd(second, Rand() * 8640000, Getdate()) 
      SET @D2=Dateadd(second, Rand() * 1000, @D1) 

      INSERT INTO Access 
      VALUES     (Floor(Rand() * 2 + 1), @D1, @D2); 
      SET @I=@I - 1 
  END 

SET @I=1 
SET @D1=Getdate() 

WHILE ( @I < 6 ) 
  BEGIN 
      SET @D2=Dateadd(day, 15, @D1) 

      INSERT INTO Periods 
      VALUES     (Concat('Period_', @I), 
                  @D1, 
                  @D2); 

      SET @D1=@D2 
      SET @I=@I + 1 
  END 

go

Working code:

-- Getting the values
DECLARE @COLS NVARCHAR(max) 

SET @COLS = Stuff((SELECT ',' + Quotename(NAME) 
                   FROM   Periods 
                   GROUP  BY NAME 
                   ORDER  BY NAME 
                   FOR xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '' 
            ) 

DECLARE @SQL NVARCHAR(max) 

SET @SQL = N'SELECT * FROM
( 
           SELECT     u.UserName, 
                      p.Name                                                  AS Period,
                      dbo.Totime(Sum(Datediff(SECOND,a.StartTime,a.EndTime))) AS [Time] 
           FROM       Access a 
           INNER JOIN Users u 
           ON         a.UserId=u.UserId 
           INNER JOIN Periods p 
           ON         p.StartTime<=a.StartTime 
           AND        a.StartTime<p.EndTime 
           GROUP BY   u.UserName, 
                      p.Name ) x PIVOT ( Max([Time]) FOR Period IN (' + @COLS +') 
) p;' 

--PRINT @SQL 

EXECUTE(@SQL)

How can I run my custom function and query in a loop for different time frames?

3 Answers3

Linked