1

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.

4
  • Why not leave the number as it is? What do you gain by changing making number 5 change to 1? It's still the lowest number. Is there some business rule that requires the lowest number to be 1? Jul 15 '16 at 16:22
  • The production group specifically requested it. At one point we had a program that did leave it, and it quickly grew to a very high number. Lowering the number regularly just makes the most sense for our situation, unfortunately..
    – Jack
    Jul 15 '16 at 16:55
  • Is the order number the same across machines? Jul 15 '16 at 17:13
  • I'm not sure I understand the question. If there are six machines, each machine has an entire set of orders with their own ordering. One machine could have orders whose sequence is 25-100, while another could be 10-20 depending on what orders have been completed.
    – Jack
    Jul 15 '16 at 17:16
3

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
1
  • 1
    You are correct in assuming that there are additional columns, I just left those out as I thought it would just clutter the question. This procedure is only going to run during certain times when it is guaranteed that other changes will not be made to the table, so I don't think locking the table is a problem. That being said, I was able to rewrite your example code to fit my needs, and it works perfectly. I've never even seen common table expressions before. I guess you learn something new everyday. Thank you!
    – Jack
    Jul 15 '16 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.