Reorganizing a picklist based on aisle direction

Question

So I will explain the entire problem first, then explain where the problem is in SQL coding for me.

I have a set of data that is a picklist through a warehouse, I am essentially trying to reorganize this data to follow a one direction in each aisle.

so for example, here is a picklist

tripid stopseq AISLE bin

216 1 PV 71

216 2 PM 64

216 3 PL 47

216 4 PM 36

216 5 PL 32

216 6 PL 88

216 7 PJ 49

216 8 PJ 29

216 9 PJ 20

216 10 PJ 19

216 11 PI 22

216 12 PI 45

216 13 PN 33

216 14 PN 28

since aisles can only go one way, some are ascending and some are descending

in this case, I want to reorganize this table so that aisle PJ is ascending(picks 7,8,9,10) so I would like it to go through the picklist and reorder the picksequence based on the aisles being ascending or descending. I will only focus on one aisle for this example, So im looking for a query that will loop through the table, and reorganize records with column PJ so that is goes in the opposite order. similar to

216 7 PJ 19

216 8 PJ 20

216 9 PJ 29

216 10 PJ 49

but I am only trying to affect those rows right now. So far I have created a cursor, with a CTE inside of it. Something like this.

declare inner_cursor cursor scroll
for select aisle from table_input

open inner_cursor

fetch next from inner_cursor into @aisle
while @@fetch_status = 0
begin
    if @aisle in ('PJ')
    begin 
      with C as
            (select stopseq, 0 + row_number() over (order by bin desc) as newtripstop
            from SIM_Input_reschedule
            )
            update C 
            set tripstopseq = newtripstop
        end
     

but that will only order the whole list by the Bin number, so I tried to add a constraint for aisle by adding

begin
            with C as
            (select tripstopseq, 0 + row_number() over (order by bin asc) as newtripstop, aisle
            from SIM_Input_reschedule
            where AISLE = @aisle
            )
            update C 
            set tripstopseq = newtripstop
        end

but that affected no rows in the table. Any help is appreciated. I might have been taking an entirely wrong approach to this with the CTE, so just say if you think of a better way to do it.

Answer 1

What I think you can do is this.

Add field sort_order which will only have two values:

• 1 - for aisles that should be sorted in ascending order
• (-1) - for aisles with desc sort order

Then you can use following query to results in correct sort order:

SELECT aisle, bin, 
  ( SELECT MAX(sequence) FROM myTable 
    WHERE t.aisle != aisle and sequence < t.sequence)
  + ROW_NUMBER() OVER(PARTITION BY aisle ORDER BY sort_order * bin) as newsequence
FROM myTable t

To do this without the new field you will have to use CASE WHEN with same logic.