Suppose I have a table taking care of incoming:
Date amount unit_price($)
01/01/05 5 7
01/02/05 4 2
01/03/05 6 3
01/04/05 3 6
01/05/05 8 1
Also have a table taking care of outgoing:
Date amount cost($)
01/01/05 2
01/02/05 7
01/03/05 6
01/04/05 3
01/05/05 4
I would like to define the outgoing cost using "First in First out", so the cost should be calculated as:
Date amount cost($)
01/01/05 2 2*$7
01/02/05 7 3*$7+4*$2
01/03/05 5 5*$3
01/04/05 3 1*$3+2*$6
01/05/05 4 1*$6+3*$1
My question is: hot to calculate these costs using sql?
Thanks!