I have a view contains all items movements (In/Out transactions) and i want to calculate item cost for each item based on method of calculation (Avg. cost, FIFO, LIFO). I need my end result to be like the following:
ItemID | Date | Transaction Type | Qty In | Qty Out | Unit Price | Total Amount | Remaining Qty | Remaining Cost
Items Table has field identify cost method (1= Avg. Cost, 2= FIFO, 3= LIFO)
based on cost calculation method result should be changed for each item.
The last two fields (Remaining Qty and Remaining Cost) should be calculate with each record.