FIFO Stock Valuation

See more:

SQL

Dear Sirs/Mams,
I am trying for FIFO Stock Valuation as follows, but not understanding how to do.

Data Input Table:
DocNo Date InnQty InnRate OutQty OutRate
1 12/02 12 12.50
2 12/03 13 ?
20 12/04 50 10.20
10 12/10 100 11.25
11 12/25 25 ?
14 12/29 150 ?


We need to populate the data according to FIFO method as following:

for 1st OutQty(13 Pcs)=12 Pcs @12.50+1Pcs*10.20=160.20/13=13 email@removed

for 2nd OutQty(25 Pcs)=25 Pcs @ 10.20 (Because 2nd InnQty has still sufficient Left(50-1-25)=24Pcs for next outqty)

for 3rd OutQty(150 Pcs)=24 Pcs @10.20+100Pcs*11.25+26 Pcs*11.25 (From Last InnQty as there is no more InnQty left) email@removed


Data Output Query by CTE & Running Total Approach:

DocNo Date InnQty InnRate OutQty OutRate
1 12/02 12 12.50
2 12/03 13 12.32
20 12/04 50 10.20
10 12/10 100 11.25
11 12/25 25 10.20
14 12/29 150 11.08



Kindly help me out.

Regards-
Sanjeeb