March 25, 2013 at 3:20 am
hi,
I have a table
ID UserID Purpose DateCreated
1 500 login 2013-03-24 14:39:43.273
2 501 login 2013-03-24 14:39:43.277
3 502 login 2013-03-24 14:39:43.277
4 503 login 2013-03-24 14:39:43.277
5 500 logout 2013-03-24 14:44:43.280
6 501 logout 2013-03-24 14:44:43.280
I need to calculate the time spent on total for each user id
the result needs to look something like
UserID TotalTime
500 25
501 25
502 25
503 25
i've been trying a bunch of thing, and i think i need to make cases for the login/logout but it is not working...
this is what i wrote that wrong in so many levels :w00t:
select userid, sum(datediff(mi,loginn,logoutn)) as TotalTime,
case when Purpose like '%login%' then DateCreated else 0 end as [loginn],
case when Purpose like '%logout%' then DateCreated else 0 end as [logoutn]
March 25, 2013 at 3:37 am
the below SQL should do what you need and also covers when people login and out multiple times.
I have assumed if they havent logged out they are still logged in
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects o
WHERE o.xtype IN ('U')
AND o.id = OBJECT_ID(N'tempdb..##TlbLog'))
DROP TABLE ##TlbLog
CREATE TABLE ##TlbLog
(
ID INT
,UserID INT
,Purpose VARCHAR(20)
,DateCreated DATETIME
)
INSERT INTO ##TlbLog
(ID, UserID, Purpose, DateCreated) VALUES
(1, 500, 'login' ,'2013-03-24 14:39:43.273')
,(2, 501, 'login' ,'2013-03-24 14:39:43.277')
,(3, 502, 'login' ,'2013-03-24 14:39:43.277')
,(4, 503, 'login' ,'2013-03-24 14:39:43.277')
,(5, 500, 'logout' ,'2013-03-24 14:44:43.280')
,(6, 501, 'logout' ,'2013-03-24 14:44:43.280')
,(2, 501, 'login' ,'2013-03-24 14:55:43.277')
,(6, 501, 'logout' ,'2013-03-24 15:55:43.280')
SELECT li.UserID
, SUM( DATEDIFF(mi, li.DateCreated, CASE WHEN lo.DateCreated IS NULL THEN GETDATE() ELSE lo.DateCreated END ) )TotalTime
FROM (
SELECT ID
, UserID
, Purpose
, DateCreated
, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY DateCreated ) Rnum
FROM ##TlbLog
WHERE Purpose = 'login' ) li
LEFT JOIN (
SELECT ID
, UserID
, Purpose
, DateCreated
, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY DateCreated ) Rnum
FROM ##TlbLog
WHERE Purpose = 'logout' ) lo ON li.UserID = lo.UserID AND li.Rnum = lo.Rnum
GROUP BY li.UserID
March 25, 2013 at 3:45 am
it worked!!!!! 😀
thanks so much!
i just need to understand what you did!
thanks again 😀
March 25, 2013 at 3:57 am
no problems.
what i did was break out the login and logout sets and assign a row number to each login and log out. Then join this data back on userid and row number using a left join so where users that havent logged out can be defaulted to the date and time of running the SQL so you can calc the minutes passed for each login and logout.. then grouping by the userid allows you to sum all logins and logouts into one row
hope ive explained well enough for you.
🙂
it could probably be written neater and with less code..
if you have access top sql 2012 box there is a function called LEAD which can read forwards or backwards over rows making a job like this far easier.
March 26, 2013 at 1:31 am
thanks!!! what i meant is that i was going to try to understand lol.
but i did on my own before reading my explanation... (there goes my ego :-D)
i am just trying to learn sql, and than for the lead thingy, i will look to see what it is as i do use sql 2012
thanks again 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply