Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert data from TableA to TableB Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2007 4:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 7:48 AM
Points: 17, Visits: 52
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 :

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!
Post #418957
Posted Tuesday, November 6, 2007 4:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 5,318, Visits: 9,760
Mmm.. sounds like you're asking us to do the whole of your homework/prjoect work for you! What have you tried so far? Here's a hint: you can't use a function to do inserts and updates: use a stored procedure instead.

John
Post #418961
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse