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
timestamptimestamp,
WorkOrderInt,
LineInt,
PriorityInt
)
--===== 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);
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);
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
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);
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply