Insert data from TableA to TableB

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 function 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 sql function be created for the above action? Thanks guys!

 

Use the following scripts,

Code:

declare @Qty as int
declare @ItemID as varchar(10)
declare @Units as int
declare @Item_No as int
set @Units=3
set @Item_No=1235
declare @TotQty as int
declare @iQty as int

set @TotQty=(select sum(Qty) from TableA where Item_No=@Item_No)

if  @TotQty<=@Units 
begin
    insert into TableB select * from TableA where Item_No=@Item_No
    update TableA set Qty=0 where Item_No=@Item_No
end
else
begin
    set @TotQty=0
    DECLARE Cur CURSOR FOR
      select Qty,Item_ID from TableA where Item_No=@Item_No order by Dates
    OPEN Cur
    FETCH NEXT FROM Cur INTO @Qty,@ItemID
    WHILE @@FETCH_STATUS = 0
    BEGIN

        set @TotQty=@TotQty+@Qty

        if  @TotQty<=@Units 
        begin
            insert into TableB select * from TableA where Item_ID=@ItemID                                            
            update TableA set Qty=0 where Item_ID=@ItemID    
            
        end
        else
        begin
            set @iQty=@TotQty-@Units    
            if  @TotQty<=@Units 
            begin
                insert into TableB select [Dates],[Item_ID],
                    [Item_No],@Units-(@TotQty-@Qty),[Unit_Price] from TableA where Item_ID=@ItemID                                                            
                update TableA set Qty=@iQty where Item_ID=@ItemID    
            end    
        end

    FETCH NEXT FROM Cur INTO @Qty,@ItemID
    END
    CLOSE Cur
    DEALLOCATE Cur
end

Note: You referred the Item_ID as int , but values used for that columns as IT1000,IT1001,IT1002,IT1003
So I declared as varchar(10) type.