title: Calculation average costing using with recursive sql (postgres 9.1)
body: <p>The closest thing I got from searching this site is this :
  <a href="https://stackoverflow.com/questions/5396827/inventory-average-cost-calculation-in-sql">Inventory Average Cost Calculation in SQL</a></p>
  
  <p>But unfortunately it was oracle specific, using model clause.</p>
  
  <h2>So let's begin.</h2>
  
  <p>There are two tables: 
  <br>-the one that holds inventory transactions, and 
  <br>-the one that holds the latest inventory valuation</p>
  
  <p>I am trying to make an inventory valuation report using average costing method based on a certain date.
  Doing it the normal way, calculating from the beginning until that specific date, will yield variable response time.
  <br>
  Imagine calculating on five years worth of data ( and thousands different inventory items ).
  It will take considerable amount of time ( and my company is not silicon-valley grade. meaning, 2 core cpu and 8 GB of RAM only)
  so I am calculating it backwardly: from the latest (current) backtrack to that specific date.</p>
  
  <p>(Every month the accounting dept will check on data, so the calculation will only deal with 1 month's worth of data, forever.
  equal to consistent unchanging performance)</p>
  
  <p><br> I have merged the table into one on the script below</p>
  
  <pre><code>create table test3 ( rn integer, amt numeric, qty integer, oqty integer);
  insert into test3 (rn,amt,qty,oqty) values (0,2260038.16765793,8,0);
  insert into test3 (rn,amt,qty,oqty) values (1,1647727.2727,3,0);
  insert into test3 (rn,amt,qty,oqty) values (2,2489654.75326715,0,1);
  insert into test3 (rn,amt,qty,oqty) values (3,2489654.75326715,0,1);
  insert into test3 (rn,amt,qty,oqty) values (4,1875443.6364,1,0);
  insert into test3 (rn,amt,qty,oqty) values (5,1647727.2727,3,0);
  insert into test3 (rn,amt,qty,oqty) values (6,3012987.01302857,0,1);
  insert into test3 (rn,amt,qty,oqty) values (7,3012987.01302857,0,1);
  
  select * from test3; (already sorted desc so rn=1 is the newest transaction)
  
  rn  amt        qty  oqty
  0   2260038.168 8   0    --&gt; this is the current average
  1   1647727.273 3   0
  2   2489654.753 0   1
  3   2489654.753 0   1
  4   1875443.636 1   0
  5   1647727.273 3   0
  6   3012987.013 0   1
  7   3012987.013 0   1
  
  
  with recursive 
  runsum (id,amt,qty,oqty,sqty,avg) as 
      (select data.id, data.amt, data.qty, data.oqty, data.sqty, data.avg
       from (
          select rn as id,amt,qty, oqty, 
          sum(case when rn=0 then qty else 
               case when oqty=0 then qty*-1 
                  else oqty end end) over (order by rn) as sqty, lag(amt) over (order by rn) as avg
            from test3 ) data
           ),
  trans (id,amt,qty,oqty,sqty,prevavg,avg) as
      (select id,amt,qty,oqty, sqty,avg,avg
       from runsum 
       union
      select runsum.id,trans.amt,trans.qty, trans.oqty, trans.sqty, lag(trans.avg) over (order by 1), 
      case when runsum.sqty=0 then runsum.amt else 
      ((trans.prevavg*(runsum.sqty+trans.qty))-(runsum.amt*trans.qty)+(trans.prevavg*trans.oqty))/(runsum.sqty+trans.oqty)
      end
      from runsum join trans using (id))
  select * 
  from trans
  where prevavg is null and avg is not null
  order by id; 
  </code></pre>
  
  <p>The result is supposed to be like this</p>
  
  <pre><code>rn  amt        qty   oqty   sum avg
  1   1647727.273 3   0   5   2627424.705
  2   2489654.753 0   1   6   2627424.705
  3   2489654.753 0   1   7   2627424.705
  4   1875443.636 1   0   6   2752754.883
  5   1647727.273 3   0   3   3857782.493
  6   3012987.013 0   1   4   3857782.493
  7   3012987.013 0   1   5   3857782.493
  </code></pre>
  
  <p>but instead I get this</p>
  
  <pre><code>id  amt        qty   oqty   sqty    avg
  1   1647727.273 3   0   5   2627424.705
  2   2489654.753 0   1   6   2627424.705
  3   2489654.753 0   1   7   2627424.705
  5   1647727.273 3   0   3   3607122.137 --&gt; id=4 is missing thus                       
                                                              screwing the calculation 
                                                         and id=6 in turn dissappears tpp
  7   3012987.013 0   1   5   3607122.137
  </code></pre>
  
  <p>I am flabbergasted.
  Where is the mistake?</p>
  
  <p>Thank you for your kind help.</p>
  
  <p><strong>EDITED</strong></p>
  
  <p>Average Costing Method backtracking ( given current avg calculate last transaction avg, and so on until nth transactions )</p>
  
  <pre><code>Avg (n) = ((Avg(n-1) * (Cum Qty(n)+In Qty(n))) - (In Amount(n) * In Qty (n)) + (Avg(n-1) * Out Qty(n))/(Cum Qty(n)+Out Amount(n))
  </code></pre>
  
  <p>Cumulative qty for backtracking transactions would be minus for in, plus for out.
  So if current qty is 8, transaction in qty before is 3, then cumulative qty for that transaction is 5.</p>
  
  <p>To calculate the average for one transaction before last, then we use current average to use in that transaction calculation.</p>
  
  <p><strong>CURRENT ANSWER BY @kordirko's help</strong></p>
  
  <pre><code>with recursive 
  runsum (id,amt,qty,oqty,sqty,avg) as 
      (select data.id, data.amt, data.qty, data.oqty, data.sqty, data.avg
       from (
          select rn as id,amt,qty, oqty, 
          sum(case when rn=0 then qty else 
               case when oqty=0 then qty*-1 
                  else oqty end end) over (order by rn) as sqty, lag(amt) over (order by rn) as avg
            from test3 ) data
           ),
  counter (maximum) as
           (select count(rn)
            from test3
           ),
  trans (n, id,amt,qty,oqty,sqty,prevavg,avg) as
      (select 0 n, id,amt,qty,oqty, sqty,avg,avg
        from runsum 
       union 
      select trans.n+1, runsum.id,trans.amt,trans.qty, trans.oqty, trans.sqty, 
      lag(trans.avg) over (order by 1), 
      case when runsum.sqty=0 then runsum.amt else 
      ((trans.prevavg*(runsum.sqty+trans.qty))-(runsum.amt*trans.qty)+(trans.prevavg*trans.oqty))/(runsum.sqty+trans.oqty)
      end
      from runsum join trans using (id)
      where trans.n&lt;(select maximum*2 from counter))
  select * 
  from trans
  where prevavg is null and avg is not null
  order by id; 
  </code></pre>