Displaying time ranges based on login, logout

I have a table that lists users who have logged in and out of a server. The table lists the PID, which is a number...

for their ID, an event -- either logged in or logged out, and a timestamp. I threw in a small sample table below... How do I write an SQL statment to display the PID and time the user was logged in to the server?

PID	Event	TimeStamp
1234	Login	10:00
5678	Login	10:20
1234	Logout	11:30
5678	Logout	11:40

The trick here is to match up each login with its logout. Again, a scalar subquery in the SELECT clause comes to the rescue. But first, let's create and populate our table (note: this is Oracle flavored SQL):

create table ConnectionLog
( PID number(4),
  Event varchar(6),
  Logged date
);

insert into ConnectionLog values (1234, 'Login',
  to_date('12-MAR-2002 10:00','DD-MON-YYYY HH24:MI'));
insert into ConnectionLog values (5678, 'Login', 
  to_date('12-MAR-2002 10:20','DD-MON-YYYY HH24:MI'));
insert into ConnectionLog values (1234, 'Logout',
  to_date('12-MAR-2002 11:30','DD-MON-YYYY HH24:MI'));
insert into ConnectionLog values (5678, 'Logout', 
  to_date('12-MAR-2002 11:40','DD-MON-YYYY HH24:MI'));

select PID, ( ( select min(Logged)
                from ConnectionLog
                where PID = t.PID
                  and Logged > t.Logged )
              - Logged 
            ) * 24 * 60 as DurationInMinutes
  from ConnectionLog t
  where Event = 'Login';

PID  DURATIONINMINUTES
---- -----------------
1234    90
5678	80

For More Information

• What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.