I have this table (see Text file)
Basically, I would like is, based on the Rank column, where 1 is most prioritized,
If within a parameter I passed a UPC Number, lets say we take 803500000569 (its rank is 2)
I would like to insert in another table or I could add a column in the same table and do an update (whatever the easiest)
and write a "Final Rank" to it. That finalRank would be a kind of Row Number so that every line in that table would get a number. no duplicates. Let me explain
If I send as the first param upc 803500000569, it would start at FinalRank 1.
then I send as param another upc lets say 803500923127. this upc is on Rank 1 and Rank 2. so it go on FinalRank 1 and the previous UPC would take the FinalRank 2
then is I take upc 803500923110 you see in text file this one is Rank 1 on two rows but if you look at the Cust Column, its the same Customer number, it means that upc belongs to the same order so it must go within the rank of that customer to try to complete the order the fastest. so that UPC would take FinalRank 2 and the previous upc on FinalRank 2 would go FinalRank 3
Then after for a last example, if I take UPC 803500000590, well that one is Rank 2, its a new customer number also so I dont have to try to match it, I could put it FinalRank 4 for now ..
So at the end, I could have multiple solutions, all them good, but it jsut need to respect the order of "Rank" and Customer orders
Could you help me to work this out with a While statement or I dont know, a temp table with some insert or update, I dont from there where to go.
Let me know if I wasnt clear enough on my explanations....