SQL Server 2008 R2 – Use Stored Procedure to Decrement Minimum Column Value

aggregatesql-server-2008-r2stored-procedures

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,

Order No.

1

2

3

4

5

Then, orders 1, 2, 3, and 4 are completed, and new orders are added on, so you have:

5

6

7

8

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.

Best Answer

This example code should work, but there are some things to watch out for. Since this is updating the tables while (presumably) another process is using that table to schedule jobs, concurrency could be an issue.

For example, I'd assume there is another key field not mentioned that the scheduler process is using to identify the jobs. If it's the xNumber/xMachine combination, then changing them could cause an issue.

    CREATE TABLE #xTable (xNumber INT , xMachine INT)

    INSERT #xTable ( xNumber, xMachine ) VALUES (5,1),(6,1),(7,1),(8,1),(9,1),(7,2),(8,2),(9,2)
    SELECT * FROM #xTable AS xt;

;
WITH    cte ( xMin, xMachine )
        AS ( SELECT   MIN(xNumber)
            ,       xMachine
            FROM        #xTable
            GROUP BY xMachine
           )
    UPDATE     #xTable
    SET        xNumber -= cte.xMin - 1
    FROM       #xTable
    INNER JOIN cte ON cte.xMachine = #xTable.xMachine;

    SELECT * FROM #xTable AS xt

    DROP TABLE #xTable;

Initial values:

xNumber xMachine
5   1
6   1
7   1
8   1
9   1
7   2
8   2
9   2

Modified Values:

xNumber xMachine
1   1
2   1
3   1
4   1
5   1
1   2
2   2
3   2
Related Question