November 6, 2007 at 4:04 am
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!
November 6, 2007 at 4:28 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply