May 3, 2012

Simple Query to Calculate FIFO

I assume that you have table below :
tbl_purchasing
-------------------------------------------------------------------------------------
purchase_number - item_id - date_purchase - unit_cost - qty  - last_qty
-------------------------------------------------------------------------------------
001 - A2 -11/11/2012 - 1000 - 120 - 50
002 - A2 -11/11/2012 - 1100 - 100 - 100
003 - A2 -13/11/2012 - 1350 - 10 - 10
-------------------------------------------------------------------------------------
Note : last_qty is available qty to sale. In this case, it mean 70 was sold.

Sample case :
at 14/11/2012 we need to input sales invoice with item A2 and qty = 152.
How to calculate price using FIFO method?
Aha! as easy as you think.. :p lol
A simple query will solve that :

set @param:=40;
set @counter:=40;

select purchasing.purchase_number, purchasing.sisa,purchasing.unit_cost,
case when @param <= purchasing.sisa then @param else purchasing.sisa end * unit_cost harga,

@param := @param - purchasing.sisa as sampah_masyarakat

from
   (
        select doc_id,sum(last_qty) sisa,unit_cost
        from tbl_purchasing
        where item_id = 'A2'
        group by unit_cost
    order by date_purchase,purchase_number
     ) purchasing
having (harga > 0)

just change 40,A2 into variable and ..
Done!

Very simple, even dont need to create func,proc,trigger, whatever..
Juat a simple single query!
Created by me with some reference from stackoverflow.com

Enjoy..
Cheers!


Note : tested in mysql not sure run in another..

2 comments:

  1. Anonymous12/01/2014

    can you provide some others table in full structure? btw thanks for your direction.

    ReplyDelete
  2. Really hoping you are still monitoring this 2012 post, would like to ask a question please.

    ReplyDelete

Are legacy 4GL applications keeping you from embracing modern technologies?

As the competitive advantages offered by cloud, mobile, and other new technologies become more apparent, the decision to migrate legacy 4GL ...