Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

calculating time difference Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 3:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:17 PM
Points: 49, Visits: 96
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

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]
Post #1434784
Posted Monday, March 25, 2013 3:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:53 AM
Points: 1,726, Visits: 490
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

Post #1434791
Posted Monday, March 25, 2013 3:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:17 PM
Points: 49, Visits: 96
it worked!!!!!
thanks so much!
i just need to understand what you did!
thanks again
Post #1434795
Posted Monday, March 25, 2013 3:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 2:53 AM
Points: 1,726, Visits: 490
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.
Post #1434799
Posted Tuesday, March 26, 2013 1:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, July 26, 2014 11:17 PM
Points: 49, Visits: 96
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 )
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
Post #1435291
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse