Styjun

I have a SQL Server table that has records with one filed being a number that I need to renumber into a new table, It also has an identity column that would repeat on records when there are multipe. I would like to sort the table on the identity field, then descending on the number field then use a cursor to number the 1st record (1) the next record (2) and so on until the identity field changes, then repeat.

Fields I have are: load_number (identity), stop_type, stop_number
Data would look like so:

load_number -- stop_type -- stop_number

1234 ------------------ 1 ------------------- 1

1234 ------------------ 2 ------------------- 5

1234 ------------------ 2 ------------------- 4

1234 ------------------ 2 ------------------- 3

1234 ------------------ 2 ------------------- 2

1234 ------------------ 2 ------------------- 1

I have other fields that describe each row in more detail, but what I need is for the above to have 5 become 1, 4 become 2, 3 stay 3, 2 become 4 and 1 become 5 (so flip the numbers completely) these numbers could go as high as 10.

DECLARE @lead_key int = 1, @load_number int, @stop_type, @stop_number, @stopnum

CREATE TABLE #renumber
 (load_number int, stop_type int, stop_number int)

DECLARE stop_cursor CURSOR FAST_FORWARD FOR
 select load_number, stop_type, stop_number 
 from sometable 
 order by load_number, stop_type, stop_number desc

OPEN stop_cursor

FETCH NEXT from stop_cursor
INTO @load_number, @stop_type, @stop_number

while @@FETCH_STATUS = 0
BEGIN

If @load_key = 1
 BEGIN
 SET @load_key = @load_number --initialize @load_key for first record
 END

If @load_key = @load_number
 BEGIN
 If @stop_type = 2
 BEGIN
 insert into #renumber
 select @load_number, @stop_type, @stopnum --use @stopnum counter
 SET @stopnum = @stopnum + 1
 END
 ELSE
 BEGIN
 insert into #renumber
 select @load_number, @stop_type, @stop_number --use @stop_number
 SET @stopnum = 1 --reset @stopnum to 1
 END
 END
ELSE
 SET @load_key = @load_number
 SET @stopnum = 1
END

FETCH NEXT from stop_cursor
INTO @load_number, @stop_type, @stop_number

CLOSE stop_cursor;
DEALLOCATE stop_cursor;

This query loops and never ends, my ability to debug isn't working on my PC so I can't step through this to see where it's looping so I thought I'd ask here the correct way to get what I am looking for. Thanks for any help!

I do not quite understand the question, I just put my idea that you can create a temporary table with an identity column, then do update on that table with a static cursor.

Step 1:

CREATE TABLE #renumber
 (ID int identity(1,1), load_number int, stop_type int, stop_number int)

Step 2: Insert table with the Identity created automatically

insert into #renumber(load_number, stop_type, stop_number)
select load_number, stop_type, stop_number 
from sometable 
order by load_number, stop_type, stop_number desc

Step 3:

DECLARE @id int, @load_number int, @stop_type, @stop_number, @stopnum int

--Static cursor

DECLARE stop_cursor CURSOR STATIC FOR
 select id, load_number, stop_type, stop_number 
 from #renumber
 order by id

OPEN stop_cursor

FETCH NEXT from stop_cursor INTO @id, @load_number, @stop_type, @stop_number

while @@FETCH_STATUS = 0
BEGIN

If @load_key = 1
 BEGIN
 SET @load_key = @load_number --initialize @load_key for first record
 END

If @load_key = @load_number
 BEGIN
 If @stop_type = 2
 BEGIN
 --Update the value you'd like base on ID
 update #renumber set stopnum = @stopnum where id = @id
 SET @stopnum = @stopnum + 1
 END
 ELSE
 BEGIN
 --Update the value you'd like base on ID
 update #renumber set stopnum = @stopnum where id = @id 
 SET @stopnum = 1 --reset @stopnum to 1
 END
 END
ELSE
 SET @load_key = @load_number
 SET @stopnum = 1
END

FETCH NEXT from stop_cursor INTO @id, @load_number, @stop_type, @stop_number

CLOSE stop_cursor;
DEALLOCATE stop_cursor;