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.
3 Answers
Answers 1
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.
Answers 2
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 !
Answers 3
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) 
0 comments:
Post a Comment