Is there a way to do this without a Cursor

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

SQL Server 2000 Forums

SQL Server Development (2000)

Is there a way to do this without a Cursor

Author

Topic

adrox
Starting Member

17 Posts

Posted - 2005-05-11 : 11:32:56

-- Get all the members (ugh)
DECLARE c_GetAllStreaksCurr CURSOR
FOR SELECT ms.MemberID, ms.dnHowLongStreak HowLong
FROM MemberStreak ms
ORDER BY ms.dnHowLongStreak DESC
FOR READ ONLY

OPEN c_GetAllStreaksCurr;
BEGIN TRAN
-- Get the first row and friends
FETCH c_GetAllStreaksCurr INTO @lv_nMemberID, @lv_nHowLong
SELECT @X = 1, @Y = @X, @lv_nPreviousHowLong = @lv_nHowLong

-- Loop thru the members, and figure out the place information and do scoring based on that
WHILE (@@fetch_status = 0) BEGIN
-- Insert a row into the streak table for this
UPDATE MemberStreak
SET dnRankForHowLongStreak = @Y
WHERE MemberID = @lv_nMemberID
IF (@@error <> 0) BEGIN
ROLLBACK TRAN
GOTO theend
END

-- Get next row
FETCH c_GetAllStreaksCurr INTO @lv_nMemberID, @lv_nHowLong
SET @X = @X + 1
-- If the members are not tied, the @Y (place) to the current team position (@X)
IF (@lv_nHowLong <> @lv_nPreviousHowLong)
SELECT @Y = @X, @lv_nPreviousHowLong = @lv_nHowLong
END -- (@@fetch_status = 0)
COMMIT TRAN

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-11 : 11:58:52

ummm... it might be a little easier if you explain to us what data you have and what you need to return, with a small sample if possible.

Also, check the articles here at SQLTeam I have written one on a set-based way to calculate streaks in your data ... though I have no idea if that applies to your situation or not.

- Jeff

adrox
Starting Member

17 Posts

Posted - 2005-05-11 : 12:09:17

The query gets members and streaks so the data could be like this:

memberID howLong
2123125 22
234214 1
3242523 15
324234 15
2342525 23
32432 22

The cursor loops through and ranks based on the streak
allowing for ties. Example

memberID Rank howLong
2123125 2 22
234214 6 1
3242523 4 15
324234 4 15
2342525 1 23
32432 2 22

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-11 : 12:21:07

Hey,

There's this guy I know who wrote an article on streaks...

http://www.sqlteam.com/item.asp?ItemID=12654

Brett

8-)

adrox
Starting Member

17 Posts

Posted - 2005-05-11 : 12:33:24

I forgot to mention...The main thing I need to accomplish is ranking the members by longest streak. Ties result in having the same rank, so I need to compare the member to the previous member to see if the streak is the same.

adrox
Starting Member

17 Posts

Posted - 2005-05-11 : 12:42:36

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-11 : 12:53:40

This might need cleaning up... but it should work.


Create Table #myTable (memberID bigint, howLong int)
Insert Into #myTable
Select 2123125, 22 Union
Select 234214, 1 Union
Select 3242523, 15 Union
Select 324234, 15 Union
Select 2342525, 23 Union
Select 32432, 22
Order By 2 desc
go
Alter Table #myTable Add rank int
go
Select * From #myTable
go
Update #myTable
Set rank = (Select count(*) From #myTable B Where howLong >= A.howLong)-- and memberId<>A.memberId)
From #myTable A 
go
Update A
Set rank = A.rank-1
From #myTable A 
Inner Join #myTable B
On A.rank = B.rank
and A.memberId <> B.memberId
go
Select * From #myTable
go
Drop Table #myTable

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

adrox
Starting Member

17 Posts

Posted - 2005-05-11 : 13:26:24

Thanks Seventhnight

worked great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources