SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


calculating time difference


calculating time difference

Author
Message
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 200
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]
Terry300577
Terry300577
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1917 Visits: 514
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


astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 200
it worked!!!!! :-D
thanks so much!
i just need to understand what you did!
thanks again :-D
Terry300577
Terry300577
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1917 Visits: 514
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.

Smile

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.
astrid 69000
astrid 69000
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 200
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 :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search