Finding previous/next record based on criteria without using a cursor

Question

Finding previous/next record based on criteria without using a cursor

mzak

Mr or Mrs. 500

Points: 562
More actions
April 8, 2009 at 3:11 pm

#73598

I've done some searching and couldn't find a similar example so I'm prefacing this with the following:
1) if you read this, know of a reference and are kind enough to direct me to the reference - it is very much appreciated (please don't waste your time "rebranding" it to fit my scenario).
2) I can do this with a cursor and there would be no real issues with performance - I'm just trying to expand my horizon with this task.
3) I am bound to SQL 2000 for this project/task.
I have a table I am using to log activities (in this case, I am logging when people log in to an application and when they log out). My schema is as follows:
Id int identity(1, 1)
UserId int
StatusCode varchar(10)
StatusDate datetime
Sample data:
1, 1, Login, 4/8/2009 13:00:00
2, 1, Logout, 4/8/2009 15:00:00
3, 2, Login, 4/8/2009 15:01:00
4, 1, Login, 4/8/2009 15:01:01
5, 1, Logout, 4/8/2009 16:00:00
6, 2, Logout, 4/8/2009 16:01:00
Enforced business rules:
1) A user cannot logout if he/she is not logged in; however, an open-ended Login record can occur. The application is a web-based application (stateless environment) so the users must manually invoke the login/logout execution. The user may login to the application and then never logout. The user will never be able to execute the logout routine without a valid login record established.
2) The table id (Id) is sequentially consistent with the StatusDate (if the results are ordered by one or the other the results will be in the same sequence)
I would like to produce results that would calculate the time of each user's logged in session. For instance, based upon the sample data, I would want:
UserId, StatusCode, StatusDate, LoggedInTime
1, Login, 4/8/2009 13:00:00, Null
1, Logout, 4/8/2009 15:00:00, 120 minutes
1, Login, 4/8/2009 15:01:01, Null
1, Logout, 4/8/2009 16:00:00, 59 minutes
2, Login, 4/8/2009 15:01:00, Null
2, Logout, 4/8/2009 16:01:00, 60 minutes
I've rarely used subqueries in my select statement so I've tried a few things with some joins but my problem is always that I cannot return the maximum login date prior to the logout entry to perform the DateDiff calculation.
...a thought occurs...
So...I got this far typing this and had a revelation! I tried it and think I've got it figured out but figured I'd post this for others to "poke holes in it" or apply it to their own situations.
So taking a different approach, I joined the table with a cross join on itself as such:
Select t1.UserId, t2.StatusDate LogoutDate, Min(DateDiff(n, t1.StatusDate, t2.StatusDate)) LoginTime
From UserStatus t1
Cross Join UserStatus t2
Where t1.UserId = t2.UserId
and t1.StatusCode = 'Login'
and t2.StatusCode = 'Logout'
and t1.StatusDate < t2.StatusDate
Group By t1.UserId, t2.StatusDate
and then left joined this into my base selection query on userid and statuscode = 'Logout' and statusdate to get my LoginTime.
I think this gets me what I originally set out to do. Thanks for the help in getting to the solution!
Wow! This forum is great.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

way to go!

i can't tell you how many times that same thing has happened to me here...start forming a question, and as I'm typing i think of a possible solution.

I've got more "unposted" questions that way, where i work it out myself before i can ask the question in the first place.

keep reading here everyday! i learn something or pick up a handy snippet of code every day here.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 2

I believe you've successfully converted the Cross Join to an Inner Join with the criteria so, I think this will do the same thing...

[font="Courier New"]Select t1.UserId, t2.StatusDate LogoutDate, Min(DateDiff(n, t1.StatusDate, t2.StatusDate)) LoginTime

From UserStatus t1

Inner Join UserStatus t2

ON t1.UserId = t2.UserId

and t1.StatusCode = 'Login'

and t2.StatusCode = 'Logout'

and t1.StatusDate < t2.StatusDate

Group By t1.UserId, t2.StatusDate[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mzak Mr or Mrs. 500 Points: 562 More actions · Answer 3

Indeed.

Is there any reason I should use one join over another? I agree that the inner join is the better choice here because (like you said) I'm simply adding predicates to the cross join to turn it into an inner join.

Execution plans are the same between the two queries in this scenario.

Thanks for the insight.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I think mzak deserves a Gold Star and a big round of applause!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

mzak (4/9/2009)
Is there any reason I should use one join over another?

Use CROSS JOIN when you really do want a cartesian product. Use INNER JOIN when you want exact matches between 2 or more tables, use the various OUTER JOINS when you want all rows from one side and matching from the other.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mzak Mr or Mrs. 500 Points: 562 More actions · Answer 6

Thanks everyone. I appreciate all of the insight and am amazed at how fast the responses filed in!

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 7

mzak (4/9/2009)
Indeed.
Is there any reason I should use one join over another? I agree that the inner join is the better choice here because (like you said) I'm simply adding predicates to the cross join to turn it into an inner join.
Execution plans are the same between the two queries in this scenario.
Thanks for the insight.

Probably no difference in this case. They key is, if you practiced piano, would you practice hitting the wrong notes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

george sibbald SSC Guru Points: 104210 More actions · Answer 8

wrote:

thats sounds like the best and most succinct definition of joins I've heard.:-)

---------------------------------------------------------------------