Netflix Style Queue Priority

  • I am trying to figure something out and hitting a brick wall. If I had some code I would post it, but I just can't figure out how to even start. I’ve searched but not finding what I need.

    Each of our manufacturing lines can have multiple Work orders assigned to it. 1 is the highest priority, 2 will be the next Work Order to be produced on that line. There are 20 lines. I don't currently have a timestamp on the table but I think I will need to and can add one.

    The supervisor sets the line and priority for each Work Order. The supervisor also closes the work orders. When the Work Order for Line 1, Priority 1 is closed I need the Work Order currently set to Priority 2 for that same line to now be Priority 1, and so on throughout the rest of the Work Orders on that line.

    To further complicate this. Sometimes the supervisor will set the Work Order having Priority 3 up to the front of the line, making it priority 1. I also would then need to re-arrange the rest of the Work Orders back by 1, making the current 1 priority 2, the current 2 priority 3 etc.

    My data looks like this.

    workOrder,Line,priority

    0099952, 1, 1

    0101210, 1, 2

    0111616, 1, 3

    0125085, 2, 1

    0125240, 2, 2

    0125240, 3, 1

    I really appreciate any help i can get on this one.

  • I should have given more detail on my original posting, at the very least some code to show I am trying. I wish I had. Hopefully this will help get a response.

    This code gets the data into the correct order. I just don't know how to re-order them.

    Line is where the work order is to be done. We currently run 16 lines. Priority is the order in which all work orders on that line will be performed. I put the timestamp in because that will help to get the work orders in the proper order should 2 work orders on the same line have the same priority, the most recent timestamp should get done first.

    I don't really know how to use a cursor. I have heard many grumblings about staying away from them, but I am really at a loss.

    declare @key as int

    declare @Priority as int

    set @key = 1028

    set @Priority = 1

    update FPD_Schedule

    set Priority = @Priority

    where [Key] = @key

    after I do this, I can pull the data in the correct order I need it to be in using the query below.

    select ,

    timestamp,

    WorkOrder,

    Line,

    Priority,

    Status

    from FPD_Schedule

    where Status = 'R'

    and line <> 0

    order by line,

    Priority,

    timestamp desc

    Please, Please, Please help me figure out a way to loop through this and update the priority correctly based on the line. If more detail is needed please let me know, I'll be glad to update the post.

    Here is what the dataset looks like form the query above. Thank you all in advance.

  • You haven't really made it clear what you want to do...I will explain why.

    You started by saying that someone sets the priorities, and that they can move an order from priority 3 to 1 (for example) and this should make the other orders shift down the queue.

    Then you provided an example where you set an order to priority 1 (we don't know what priority it was before) but you do not require the other orders to shift down (alter priority)...

    This is contradictory without further detail.

    If the supervisor makes an order move from priority 3 to 1 and there are already some orders at priority 1, where in the queue does it go? Perhaps she has the option to jump to the top of a priority group and bump the existing orders of that priority and below "down one level"? Perhaps she also has the option to merge with an existing group thereby causing no bump?

    Also, please provide table creation scripts and sample data to help others to help you.

    See this article for details of how to do this : http://www.sqlservercentral.com/articles/Best+Practices/61537

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Thank you so much for taking the time to reply and pointing me into the direction of the boards etiquette. It was very helpful.

    There can only be one work order on a line at a time. If the supervisor sets a Work Order to 1 where there is already an existing work order on that line with a 1 priority, then all other work orders on that line would bump down by 1. (Example 1 below)

    If the work order in priority 1 gets its priority set to 4, then the existing work orders set to 2, 3, 4 would bump up to 1, 2, 3 respectively. (Example 2 below)

    If the work order in priority 1 gets its priority set to 3, then the existing work orders set to 2, 3 would bump up to 1, 2 respectively. The work order in priority 4 would not change. (Example 3 below)

    The most difficult scenarios involve when a work order moves from one line to another. This one Work Order will affect 2 separate lines. Work Order with Priority 1 moves from Line 3 to Line 4 and it's new priority is 2. Priorities 2, 3, 4 would need to bump up to 1, 2, 3. The moved Work Order would get priority 2 on line 4, Priorities 3, 4 would get bumped down to 4, 5. (Example 4 below)

    Probably doesn't need to be said, but with the examples below please only un-comment one of them at a time.

    I do really appreciate your help not only with my query but the board also. Thanks Again.

    Here's the code...

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..#FPD_Schedule','U') IS NOT NULL

    DROP TABLE #FPD_Schedule

    --===== Create the test table with

    CREATE TABLE #FPD_Schedule

    (

    [Key] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table

    timestamp timestamp,

    WorkOrder Int,

    Line Int,

    Priority Int

    )

    --===== All Inserts into the IDENTITY column

    -- SET IDENTITY_INSERT #FPD_Schedule_Test ON

    --===== Insert the test data into the test table

    INSERT INTO #FPD_Schedule

    (WorkOrder, Line, Priority)

    SELECT '100', '3', '1' UNION ALL

    SELECT '101', '3', '2' UNION ALL

    SELECT '102', '3', '3' UNION ALL

    SELECT '103', '3', '4' UNION ALL

    SELECT '104', '4', '1' UNION ALL

    SELECT '105', '4', '2' UNION ALL

    SELECT '106', '4', '3' UNION ALL

    SELECT '107', '4', '4'

    -- Example 1

    -- update #FPD_Schedule set Priority = 1 where = 4

    /*

    Desired Results

    Key 1 Priority 2

    Key 2 Priority 3

    Key 3 Priority 4

    Key 4 Priority 1

    */

    -- Example 2

    -- update #FPD_Schedule set Priority = 4 where = 5

    /*

    Desired Results

    Key 5 Priority 4

    Key 6 Priority 1

    Key 7 Priority 2

    Key 8 Priority 3

    */

    -- Example 3

    -- update #FPD_Schedule set Priority = 3 where = 5

    /*

    Desired Results

    Key 5 Priority 3

    Key 6 Priority 1

    Key 7 Priority 2

    Key 8 Priority 4

    */

    -- Example 4

    -- update #FPD_Schedule set Priority = 2, Line = 4 where = 1

    /*

    desired results

    Key 1, Priority 2, Line 4

    Key 2, Priority 1, Line 3

    Key 3, Priority 2, Line 3

    Key 4, Priority 3, Line 3

    Key 5, Priority 1, Line 4

    Key 6, Priority 3, Line 4

    Key 7, Priority 4, Line 4

    Key 8, Priority 5, Line 4

    */

    select * from #FPD_Schedule

  • Hi Skip,

    Thanks for posting the code...made life much easier.

    I have a stored procedure for you to try...note that I renamed #FPD_Schedule to FPD_Schedule so that the stored proc could update it...

    CREATE PROC move_order @key INT,@line INT,@priority INT

    AS

    SET NOCOUNT ON;

    -- collect the existing data for the key

    DECLARE @old_priority INT,@old_line INT

    SELECT @old_priority=priority,@old_line=line FROM FPD_Schedule WHERE =@key

    -- move everything up on the line we are moving from

    -- move everything down on the line we are moving to

    -- move the specified order

    UPDATE FPD_Schedule

    SET priority= CASE

    WHEN @key THEN @priority

    ELSE

    CASE

    WHEN @line=@old_line THEN priority +

    CASE

    WHEN priority BETWEEN @priority AND @old_priority

    OR priority BETWEEN @old_priority AND @priority THEN

    CASE

    WHEN @priority<@old_priority THEN 1

    WHEN @priority>@old_priority THEN -1

    ELSE 0

    END

    ELSE 0

    END

    WHEN line=@old_line AND priority>@old_priority THEN priority-1

    WHEN line=@line AND priority>=@priority THEN priority+1

    ELSE priority

    END

    END

    , line = CASE

    WHEN @key THEN @line

    ELSE [line]

    END

    WHERE line=@line OR line=@old_line

    It looks a bit horrific but that is just because I wanted to perform all updates in one statement - the alternative being to read /update

    the table two or three times...which I wanted to avoid.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Thanks MM. It's working in some ways but not in others.

    If I have things in priority 1, 2, 3, 4 and I move Priority 3 to 2, Priority 4 changes to 5. It should remain at 4.

    I also tested Example 4, moving a from one line to another. The procedure does not move the line nor change the priorities.

  • Very odd - I tested all situations here and it works fine.

    Can you post your test script for me?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • I just re-copied your sp and it is working like a charm. For the life of I don't know what I did to mess it up.

    The code does confuse me though. I am having a difficult time following the thought process. I hate to burden you, but if you have some time to explain the code a little, I'm sure I'd learn more about it. If not, I'll just tear it apart next week at the office.

    Thanks very much for your help MM. You're a real life saver.

  • Skip (7/4/2010)


    I just re-copied your sp and it is working like a charm. For the life of I don't know what I did to mess it up.

    The code does confuse me though. I am having a difficult time following the thought process. I hate to burden you, but if you have some time to explain the code a little, I'm sure I'd learn more about it. If not, I'll just tear it apart next week at the office.

    Thanks very much for your help MM. You're a real life saver.

    No problem....here goes

    CREATE PROC move_order @key INT,@line INT,@priority INT

    AS

    SET NOCOUNT ON;

    -- collect the existing data for the key

    DECLARE @old_priority INT,@old_line INT

    SELECT @old_priority=priority,@old_line=line FROM FPD_Schedule WHERE =@key

    -- move everything up on the line we are moving from

    -- move everything down on the line we are moving to

    -- move the specified order

    UPDATE FPD_Schedule

    So, this one update will take care of all three main categories of rows

    • Items in the line which originally held the order
    • Items in the line to which the order will move
    • The order which is moving

    First we can handle the order we are moving by checking for the supplied @key value in the key field and setting the required priority

    SET priority= CASE

    WHEN @key THEN @priority

    Next we handle the case where we are just changing priority on the same line:

    ELSE

    CASE

    WHEN @line=@old_line THEN priority +

    We need to either increment or decrement the priority by 1 for all orders which have a priority that lies between the original priority of the order we are moving and it's new priority.

    Suppose we move priority 4 to priority 2 then we need to adjust everything between priority 2 and 4

    1 2* 3* 4*

    This CASE statement finds orders in that priority range

    CASE

    WHEN priority BETWEEN @priority AND @old_priority

    OR priority BETWEEN @old_priority AND @priority THEN

    And this one says that is we are moving the order up the line then all other orders in the selected range must move down (+1) and vice versa (-1)

    (and if the priority of the selected order is not changing, then don't move anything (0))

    CASE

    WHEN @priority<@old_priority THEN 1

    WHEN @priority>@old_priority THEN -1

    ELSE 0

    END

    And this ELSE is for orders that are not affected by the change, so use an adjustment value of zero.

    ELSE 0

    END

    Next, we handle where the order is changing line.

    First, move the priority of orders on the old line up one.

    WHEN line=@old_line AND priority>@old_priority THEN priority-1

    Then move the orders on the new line down one.

    WHEN line=@line AND priority>=@priority THEN priority+1

    And any orders that are not affected by the order move keep their original priority....

    ELSE priority

    END

    END

    And finally, move the selected order to it's new line (even if that is the same as the old line - no harm done.)

    , line = CASE

    WHEN @key THEN @line

    ELSE [line]

    END

    And select only the lines we are affecting...

    WHERE line=@line OR line=@old_line

    You could also extend the where clause to be more selective by only selecting records from each line that will be affected (using the priority range) but it's late and I don't have time to do that now....

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Viewing 11 posts - 1 through 11 (of 11 total)

    You must be logged in to reply to this topic. Login to reply