briangriffin
Programmer
A variable table holds rownum, which is an identity column, distdate, and an empty ranking column. I'm trying to update the ranking column with the rownum, but resetting it at each change of distdate:
RowNum Distdate Ranking
1 7/26/2010 1
.......
199 7/26/2010 199
200 7/27/2010 1
.....
425 7/30/2010 1
500 7/30/2010 76
First thing I've done is set the ranking = rownum for the min distdate:
Code:
update @v_table1
set ranking = rownum where distdate = (select min(distdate) from @v_table1)
Then I update the next lowest distdate with the rownum minus the highest rownum of the previous date:
Code:
update @v_table1
set ranking = rownum - (select max(rownum) from @v_table1 where distdate = (select max(distdate) from @v_table1 where ranking is not null))
where distdate = (select min(distdate) from @v_table1 where ranking is null)
The initial specs were to do this for one week, so I just copied the above code and pasted it in five times. This works fine for the six distinct dates in the table.
However, now it may be run for a longer date range, so I want to turn it into a loop. This is what I have:
Code:
declare
@v_counter int,
set @v_counter = 1
while @v_counter < (select count(distinct distdate) from @v_table1 where ranking is not null) -- Returns 5
BEGIN
update @v_table1
set ranking = rownum - (select max(rownum) from @v_table1 where distdate = (select min(distdate) from @v_table1 where ranking is not null))
where distdate = (select min(distdate) from @v_table1 where ranking is null)
set @v_counter = @v_counter + 1
END
However, the loop is not updating as expected - it is updating the second lowest date starting with 1, but goes through the last record of the table without resetting.
I know the loop is executing the proper number of times, but why is the behavior of the update different in the loop than when I don't use a loop but instead paste the same code in multiple times?
If I change the code to "... while @v_counter < 2..." then it updates the second date properly, and the ranking of all subsequent dates remains null.
This has to be 2000 compatible, so I don't think there are any ranking functions available to me (but I could be wrong).
Thanks in advance.