I'm trying to understand how the following algorithm could be completed in SQL 2008 R2.
I have a table that contains production data. The table uses a "rolling" scheduling method, meaning that orders are assigned a value, a sequence number, to indicate the order that they will be completed. As they are completed, the order is removed. This is all well and good, except, you suddenly end up with the lowest value being over 5660. Additionally, there are different machines for different orders, with different order assignments for each machine.
For example, for one machine,
Then, orders 1, 2, 3, and 4 are completed, and new orders are added on, so you have:
What I need is the following.
Once I am at the point where the lowest number in the table is NOT 1 (even if it is only 2), take that minimum value and subtract it from every number so that everything is lowered proportionately. Based on our previous example, the output would be:
5 -> 1
6 -> 2
7 -> 3
8 -> 4
I understand the process that needs to happen, I am just unsure of how to translate that into SQL, considering that there are multiple machines.
I know that I could easily do this with a cursor, but I would prefer not to. Ideally, some type of nested statement would be best.
For example, some type of statement (pseudo code, not correct at all)
UPDATE xTable SET xNumber = (xNumber - (SELECT MIN(xNumber) FROM xTable)) WHERE xMachine = xTable.xMachine (??)
Any type of insight that you could offer would be appreciated.