Assign QTY Based on Priorities

[CptTom]

I need to assign quantities from Table_A based on Priorities in table_B when Table_B has multiple Priorities. Table_B has a required quantity based on priority and may have more than 1 priority but not more than 3 priorities.

Here is how I am trying to work it.

DECLARE @Cat_Count INT, @Unassigned_Qty INT


Select Disinct OperationCenter, make, count(Priorities) AS Cat_Count
Into temp_table FROM table_B GROUP BY OperationsCenter, make
ORDER BY OperationsCenter, make

SET @CAT_COUNT = temp_table.Cat_Count
SET @Unassigned_Qty = Table_A.OnHand

Update table_B
SET OH_QTY = and here is where I get stuck.

I need to run a While @CAT_COUNT > 1 AND @Unassigned_Qty > 0

(IF) WHEN Priority = 1 Then set On_Hand = Required - @Unassigned_Qty
SET @Unassigned_Qty = @Unassigned_Qty – What was assigned on line above
THEN
IF(WHEN) Priority = 2 Then On_Hand = Required - @Unassigned_Qty
SET @Unassigned_Qty = @Unassigned_Qty – What was assigned on line above
THEN
IF(WHEN) Priority = 3 Then On_Hand = Required - @Unassigned_Qty
SET @Unassigned_Qty = @Unassigned_Qty – What was assigned on line above
THEN
IF(WHEN) Priority = 4 Then On_Hand = Required - @Unassigned_Qty
SET @Unassigned_Qty = @Unassigned_Qty – What was assigned on line above

Always in that order, Priority 1 - 4