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 has been online on my website. After that, 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 is the example timestamp 2016-04-01 00:00:00.000 . Now I want to see the total time spent on my website, and not on 1 range, but a sequence of ranges, for example:

 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, each time running the same code?

The output of my code is above:

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

I would like to get a result like

 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 

Thus, each range or cycle above the code should be a column.

I believe pivot() will help me here, but any help aimed at running dynamic SQL (or any better solution) would be greatly appreciated.

Answer 1

Wrap your current code in a procedure with parameters, for example:

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

and using appropriate data types.

Then create a second procedure. In this case, define another temporary table, for example

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

Scroll through each period for which you want to define data. For each period, call the CalcTime stored procedure and dump the results to the temp table. Two ways to do this, use

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

or by defining a temporary table in the calling procedure, you can refer to it in the called procedure in the standard expression INSERT... SELECT...

Also in a loop, build a comma-separated line that lists all the period labels, for example

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

or,

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

Use this to build a dynamic SQL expression in the temp table, and you are done. As mentioned in the comments, there are any SO posts on how to do this, and here are links to two: first, discusses the creation of a dynamic summary operator, and the second uses similar tactics to claim non-privilege.

---

* Avoid inline spaces in object names, they will only hurt you.

** Good, sometimes you need to do this.

Answer 2

Two pseudo tables:

 persons: personId int lastname nvarchar(50) visits: personid int created datetime duration int -- (store things like duration in seconds) 

First create a list of columns, here I used the created column and converted it to a month. Thus, the result looks something like this: [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 the number of COL variables, I suggest you start with NON dynamic SQL, make it dynamic - the last.

 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 dropping the result into the temp table (created on the fly). This creates the ability to add additional columns for output, such as organization, etc. Etc.

 alter table #temp add organization nvarchar(100) 

Good luck

Answer 3

Here is a working test code. Adapt it as you wish.

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 

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