Posted by Erland Sommarskog on 02/08/06 00:25

(haroldsphsu@gmail.com) writes:
> I have the following table to log events as they happen:
>
> EventID UserID Event Time
>====== ===== ==== ====
> 1 User 1 Login 08:00:00
> 2 User 1 Logout 11:00:00
> 3 User 1 Login 13:00:00
> 4 User 1 Logout 16:00:00
>
> Is there a way to transform it into the following form?
>
> UserID Login Logout
>===== ==== =====
> User 1 08:00:00 11:00:00
> User 1 13:00:00 16:00:00
>
> I understand I can log the start and end time instead, but I was just
> wondering if the above is possible?

SELECT a.UserID, a.Login, b.Logout
FROM tbl a
JOIN tbl b ON a.UserID = b.UserID
AND b.EventID = (SELECT MIN(c.EventID)
FROM tbl c
WHERE c.UserID = a.UserID
AND c.Event = 'Logout'
AND c.Time > a.Time)
WHERE a.Event = 'Login'

However, this presumes that all logins are followed by logouts and
vice versa. With imperfect data, the output can be funky.

I've also assumed that the real-world data has date as well as time.
If you only have time, some different rules need to be found for
sessions across midnight.

For this type questions, it is always a good idea to post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired output given the sample.
o A short narrative describing the business problem .

This makes it easy to copy and paste into a query tool, and develop a
tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]