Looking for help creating a query returning login times

[Ryan Gaudet]

I have a table that contains a row each time a user logs in and logs out of a
specific application. It contains , the event datetime, computer name,
userID, event (Login or Logout) and a description field.
I have been tasked with creating a query that will check to see if a user is
ever logged in more than 13 hours. Every login event will have a
corresponding logout event but there is no "session" identifier to match them
out. If I run a query on the table to get the event and sort by date, it is
pretty clear, user logs in, next record, user logs out etc...but I'm pretty
sure that doesn't help me.
I am having a hard time coming up with a way to do this and am hoping
someone could offer some guidance as to how to approach this via SQL query
and if this can even be done through a single SQL query or if I will have to
create an SP for this. I would prefer to do this via SQL query if
possible.The DB is on an SQL 2005 server.
I appreciate any comments/suggestion and thanks in advance.

[Tom Cooper]

Something like:

With cte As
(Select EventDateTime, Computer, UserId, EventType,
Row_Number() Over (Partition By UserId, Computer Order By EventDateTime)
As rn
From YourTable
Where EventType In ('Login', 'Logout')
/* add other conditions as needed, like date time range
you are interested in */)
Select c1.UserId, c1.Computer, c1.EventDateTime
From cte c1
Left Join cte c2 On c1.UserId = c2.UserId
And c1.Computer = c2.Computer
And c1.rn = c2.rn - 1
And c2.EventType = 'Logout'
And DateDiff(ss, c1.EventDateTime, c2.EventDateTime) <= 46800 /* 13 hours
in seconds */
Where c1.EventType = 'Login'
And c2.UserId Is Null
And DateDiff(ss, c1.EventDateTime, Current_TimeStamp) > 46800
Order By c1.UserId, c1.EventDateTime;

Essentially, the above works by trying match every login to the suceending
row for that user and computer. If there isn't any match, then the
c2.UserId will be NULL. So those are the rows you want. Except you only
want rows where the login is more than 13 hours ago. This query probably
won;t be exqactly what you want. Hopefully, it will get you started.

Tom

"Ryan Gaudet" <RyanG...@discussions.microsoft.com> wrote in message
news:F8C261A2-5F96-4BFC...@microsoft.com...

[Ryan Gaudet]

Thanks so much for that. I'm going to work on this shortly.
I really appreciate the assistance.

"Tom Cooper" wrote:

> .
>