SQL help-Comparing a field to the same field in a different record o

[ninners]

I'm running SQL Server 2005 and have the following question.
I have a table which tracks the events created by a group of users.

ID code tstamp username
574015 1 4/8/08 08:14:26 user1
575349 2 4/8/08 09:47:40 user1
575352 1 4/8/08 09:53:14 user1
579345 2 4/8/08 14:19:24 user1
579406 1 4/8/08 14:20:38 user1
583487 2 4/8/08 16:37:34 user1
592824 1 4/9/08 08:28:38 user2
600609 2 4/9/08 16:44:02 user2
593334 1 4/9/08 08:59:17 user3
594703 2 4/9/08 09:29:50 user3
594715 1 4/9/08 09:30:03 user3
600606 2 4/9/08 16:43:56 user3


code 1 = log on, code 2 = logoff. There are other codes in the table but they aren't needed so the IDs will not be sequential. I need to find the total time the user was logged into the system for each day. The user can log in and out of the system multiple times a day. So the sum of all the log in times.

The end result I was looking for was something like the example below which has the log on times for each person which would be stored in a permanent table for easier reporting.

username logontime logofftime signon day
user1 4/8/08 08:14:26 4/8/08 09:47:40 1:33 4/8/2008
user1 4/8/08 09:53:14 4/8/08 14:19:24 4:26 4/8/2008
user1 4/8/08 14:20:38 4/8/08 16:37:34 2:16 4/8/2008
user3 4/9/08 08:59:17 4/9/08 09:29:50 0:30 4/9/2008
user3 4/9/08 09:30:03 4/9/08 16:43:56 7:13 4/9/2008
user2 4/9/08 08:28:38 4/9/08 16:44:02 8:15 4/9/2008

What I have started with is I created a table which is populated daily with the log on and log off times (only codes 1 and 2) of the previous day using a SSIS package, but from there I am unsure of how to continue and get the difference in times for time each user logs on and off when I don't know what the next record for that user would be.

Thank you!!!

 

[ESquared]

What do you want to do if a logoff time is missing or if a login time is missing? You need to answer those before any solution we give you will make sense.

The easy but probably poor-performing answer is a correlated subquery:

SELECT
   E.username,
   logontime = E.tstamp,
   logofftime = E2.tstamp,
   signon = DateDiff(mi, 0, E2.tstamp - E.tstamp),
   day = Convert(datetime, DateDiff(dd, 0, E.tstamp))
FROM
   UserEvents E
   INNER JOIN UserEvents E2
      ON E2.username = E.username
      AND E2.logofftime = (
         SELECT Min(tstamp)
         FROM UserEvents
         WHERE
            code = 2
            AND username = E.username
            AND tstamp > E.tstamp
      )
WHERE
   E.code = 1
   AND E2.code = 2

There are other solutions for large amounts of data which will perform better, first a derived table answer, and fastest a temp table w/ identity column answer.

The query above will ignore logofftimes that immediately follow another logofftime without a logintime between them.

The query will also blow up if you somehow have 2 rows with same user and same logoff time.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[ninners]

What do you want to do if a logoff time is missing or if a login time is missing?"

The system has a process that runs at EOD and automatically writes a logoff event if a user foregets to logoff. I don't think it is possible and have yet to come across a situation where the user is logged on to the system but does not have a logon time.

I tried your code with a small set of sample data and I think its what need or at least give me a place to start with. I will play around with it and post any questions I have.

Thank you!