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