Matching records within same table

Hi all,

I've got a table named tLogonInfo with the following fields: fLogType, fDeskID, fUsername, fEventDate.

This table contains the logon and logoff history of an application. So for example the table could contain the following records:

1. 'Logon', '1', 'Jack', '2007-7-15 13:00:00'

2. 'Logon', '3', 'Paul', '2007-7-15 13:22:00'

3. 'Logoff', '1', 'Jack', '2007-7-15 14:00:00'

4. 'Logon', '2', 'Jack', '2007-7-16 10:30:00'

5. 'Logoff', '3', 'Paul', '2007-7-16 11:12:00'

6. 'Logoff', '2', 'Jack', '2007-7-16 17:10:00'

I want to make a query that has the fields Desk, User, Logon, Logoff and has the following result:

1. '1', 'Jack', '2007-7-15 13:00:00', '2007-7-15 14:00:00'

2. '3', 'Paul', '2007-7-15 13:22:00', '2007-7-16 11:12:00'

3. '2', 'Jack', '2007-7-16 10:30:00', '2007-7-16 17:10:00'

How can I do this?

if you use sql server 2005,

Code Snippet

Create Table #logindata (

[Operation] Varchar(100) ,

[Mac] Varchar(100) ,

[User] Varchar(100) ,

[OperDateTime] Varchar(100)

);

Insert Into #logindata Values('Logon','1','Jack','2007-7-15 13:00:00');

Insert Into #logindata Values('Logon','3','Paul','2007-7-15 13:22:00');

Insert Into #logindata Values('Logoff','1','Jack','2007-7-15 14:00:00');

Insert Into #logindata Values('Logon','2','Jack','2007-7-16 10:30:00');

Insert Into #logindata Values('Logoff','3','Paul','2007-7-16 11:12:00');

Insert Into #logindata Values('Logoff','2','Jack','2007-7-16 17:10:00');

;With Ordered as

(

Select

*

, Row_Number() Over(Partition By [User],[Mac] Order By [User],[Mac], [OperDateTime]) OpId

From

#logindata

)

Select

Logon.[User], LogOn.Mac, Logon.OperDateTime [Logon Time], Logoff.OperDateTime [Logoff Time]

from Ordered Logon

Left Outer Join Ordered Logoff On

Logon.[User] = Logoff.[User]

and Logon.[Mac] = Logoff.[Mac]

and Logon.OpId = Logoff.OpId -1

Where

Logon.Operation='Logon'

and Logoff.Operation='Logoff'

Drop table #logindata

|||

For SQL Server 2000,

Code Snippet

Create Table #logindata (

[Operation] Varchar(100) ,

[Mac] Varchar(100) ,

[User] Varchar(100) ,

[OperDateTime] Varchar(100)

);

Insert Into #logindata Values('Logon','1','Jack','2007-7-15 13:00:00');

Insert Into #logindata Values('Logon','3','Paul','2007-7-15 13:22:00');

Insert Into #logindata Values('Logoff','1','Jack','2007-7-15 14:00:00');

Insert Into #logindata Values('Logon','2','Jack','2007-7-16 10:30:00');

Insert Into #logindata Values('Logoff','3','Paul','2007-7-16 11:12:00');

Insert Into #logindata Values('Logoff','2','Jack','2007-7-16 17:10:00');

Select

[user],

[mac],

[OperDateTime] as [Logon Time],

(Select Min([OperDateTime])

From #logindata Sub Where

[Operation]='Logoff'

And Sub.[user]=Main.[user]

And Sub.[mac]=Main.[mac]) as [Logoff Time]

from #logindata Main

Where

[Operation]='Logon'

Drop table #logindata