Event log management - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Event log management

Author

Topic

mauris
Starting Member

2 Posts

Posted - 2010-10-26 : 06:21:39

Hi all,
I have a table that acts like an event log, like this:

TIME EVENT USER
00:00 LOGIN JOE
00:04 LOGIN PAUL
00:10 LOGUT JOE
00:20 LOGIN JOE
00:30 LOGIN ANDREW
00:32 LOGUT PAUL
00:35 LOGUT JOE
00:40 LOGOUT ANDREW

I want to setup one query in order to have one record for each logged session, like this:

USER FROM TO
JOE 00:00 00:10
PAUL 00:04 00:32
JOE 00:20 00:35
ANDREW 00:30 00:40

My problem is to make into relation each login record with the corresponding logout record. They have the same user in common (of course) but the logut record should be identified like "the first logut record of the same user after his login".
Can anyone help me to setup such query?

Thank you very much
maurizio

Sachin.Nand

2937 Posts

Posted - 2010-10-26 : 07:35:23

[code]
declare @t table(TIME varchar(20), EVENT varchar(20), [USER] varchar(20))
insert @t
select '00:00','LOGIN' ,'JOE' union
select '00:04','LOGIN','PAUL' union
select '00:10', 'LOGOUT','JOE' union
select '00:20', 'LOGIN','JOE' union
select '00:30', 'LOGIN','ANDREW' union
select '00:32', 'LOGOUT','PAUL' union
select '00:35', 'LOGOUT','JOE' union
select '00:40', 'LOGOUT', 'ANDREW'

;with cte
as
(
select *,ROW_NUMBER()over(partition by event,user order by user,time)rid from @t
)
select * from
(
select [USER] [User],TIME Intime,Outtime from cte c1
cross apply(select top 1 TIME as outtime from cte c2 where c1.[USER]=c2.[USER] and c1.rid=c2.rid and c1.EVENT<>c2.EVENT)C
)t where intime<>outtime and outtime>intime

[/code]

PBUH

mauris
Starting Member

2 Posts

Posted - 2010-10-28 : 08:34:39

quote:
Originally posted by Sachin.Nand

declare @t table(TIME varchar(20), EVENT varchar(20), [USER] varchar(20))
insert @t

...

Hi, thank you so much for your (fast

)answer.
Unfortunately I discovered that I need to run the query under SQL server 2000 and some of your commands (like row_number()) were not supported

Have you got another solution ?

maurizio

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources