Re: SQL question

(haroldsphsu@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: SQL Express - Identity specification property - how to change
    ... UPDATE tbl ... You can use ALTER TABLE ALTER COLUMN to add an IDENTITY column, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Getting list of recently added IDENTITY items
    ... FROM tbl WITH ... COMMIT TRANSACTION ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: time conversion hiccup
    ... We have only seen fragments and pieces of what you have been doing. ... statements with sample data, and the desired result given the sample. ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Problem With SQL UPDATE
    ... INSERT statements with sample data. ... With the first three it's to copy and paste into a query editor to ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Many to one Select
    ... It still possible to define a query that has maximum of columns needed, ... INSERT statements with sample data. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)