SQL Server - using a cursor to find and sum login/logout times

Hi all,

New to using cursors and i was after some help.

We have a trace running to find instances of certain sp's, these basically tell us when someone logs in and someone logs out (1 row per event).

Importing into a table basically gives me process, user, datetime e.g.

'login', user1, 2007-02-15 10:21:35.590

'login', user2, 2007-02-15 10:21:36.100

'logout', user1, 2007-02-15 10:22:45.100

'login', user3, 2007-02-15 10:23:23.100

'logout', user2, 2007-02-15 10:27:54.436

'logout', user3, 2007-02-15 10:30:03.237

What i need to do is to basically get to the point where we have user, total time in system' e.g.

user1, 1min 10secs (close!)

user2, 6mins 18secs

user3, 6mins 40secs

any help much appreciated.

Thanks,

Jon

You can do this without a cursor, but this method does require that you don't have bad data. Let me explain... If EVERY login has a corresponding logout and the date/times match up nicely, you can use set based operations to perform this query.

What I present below will create a table variable to store your data. If this method works for you, simply remove the @Temp variable and change the subsequent query to pull the data from your table.

-- Setting up some sample data.

Declare @Temp Table(UserAction VarChar(20), UserName VarChar(20), ActionTime DateTime)

Insert Into @Temp Values('login', 'user1', '2007-02-15 10:21:35.590')
Insert Into @Temp Values('login', 'user2', '2007-02-15 10:21:36.100')
Insert Into @Temp Values('logout','user1', '2007-02-15 10:22:45.100')
Insert Into @Temp Values('login', 'user3', '2007-02-15 10:23:23.100')
Insert Into @Temp Values('logout','user2', '2007-02-15 10:27:54.436')
Insert Into @Temp Values('logout','user3', '2007-02-15 10:30:03.237')

-- Actual query would begin here.

Declare @Actions Table(RowId Integer Identity(1,1) Primary Key Clustered, UserAction VarChar(20), UserName VarChar(20), ActionTime DateTime)

Insert Into @Actions (UserAction, UserName, ActionTime)
Select UserAction, UserName, ActionTime
From @Temp
Order By UserName, ActionTime

Select A.UserName, B.ActionTime - A.ActionTime
From @Actions As A
Inner Join @Actions As B
On A.UserName = B.UserName
And A.RowId = B.RowId - 1
And A.UserAction = 'Login'
And B.UserAction = 'Logout'

Thanks mastros, that works very well. I couldnt get my head around the join.

on a slightly different note, i have always used temp tables as opposed to table variables. what are the advantages of doing it this way

Thanks,

Jon

Temp tables are stored in the TempDB (and actually written to disk). Table variables are stored completely in memory. Most of the time, table variables are faster than temp tables. This is not always true, so if you are really trying to optimize performance, it's best to test it both ways.