quote:

---

Originally posted by fdtoo

I have the following fields in TableA running on SQL Server 2000:

Dates as datetime
Item_ID as int (Primary key)
Item_No as int
Qty as numeric
Unit_Price as numeric

TableA consist of the following info :
======================================
Dates | Item_ID | Item_No | Qty | Unit_Price
======================================
30/10/2007 | IT1000 | 1234 | 2 | 4
======================================
30/10/2007 | IT1001 | 1235 | 2 | 6
======================================
28/09/2007 | IT1002 | 1236 | 4 | 8
======================================
01/11/2007 | IT1003 | 1235 | 2 | 2

I have to assign 3 units of Item_No 1235 from TableA into TableB, with the following rules :

assuming 3 units is a variable declared in sql stored procedure

1) assign units from the earliest date or earliest Item_ID to Qty field in TableB
=> 2 units from Item_ID : IT1001 insert to Qty field in TableB
=> Unit_Price of 6 from Item_ID : IT1001 insert to Unit_Price field in TableB

2) assign the balance of 1 unit from the next earliest date or earliest Item_ID to Qty field in TableB
=> 1 unit from Item_ID : IT1003 insert to Qty field in TableB
=> Unit_Price of 2 from Item_ID : IT1003 insert to Unit_Price field in TableB

3) Update Qty field in TableA after inserting the above quantity for Item_No 1235 into TableB

Note:sql stored procedure should be intelligent to assign the units required base on earliest date or
earliest Item_ID automatically. If units required is insufficient, it should be able to loop
for the next available units, if the units to assign is greater than the units available in TableA,
an error message should be printed, telling the user, units to assign is insufficient. If Item_No to be
assigned does not exist in TableA, an error message should be printed, telling the user, item not exist.

INSERT Result in TableB should consist the following info :

======================================
Dates | Item_ID | Item_No | Qty | Unit_Price
======================================
30/10/2007 | IT1001 | 1235 | 2 | 6
======================================
01/11/2007 | IT1003 | 1235 | 1 | 2

UPDATE Result in TableA should consist the following info :

======================================
Dates | Item_ID | Item_No | Qty | Unit_Price
======================================
30/10/2007 | IT1000 | 1234 | 2 | 4
======================================
30/10/2007 | IT1001 | 1235 | 0 | 6
======================================
28/09/2007 | IT1002 | 1236 | 4 | 8
======================================
01/11/2007 | IT1003 | 1235 | 1 | 2

Can a stored procedure be created for the above action? Thanks guys!

---