This discussion is archived
1 2 Previous Next 17 Replies Latest reply: May 13, 2008 10:27 AM by ADFBeginer RSS

Using LAG / LEAD to calculate Inventory Cost and Running Average ?

162208 Newbie
Currently Being Moderated
I want to calculate the average cost of sales, so that I should know, what is the cost of Inventory in Hand.

The Negative Quantity shows the Sales quantity, how can I calculate the Average Cost using LAG /LEAD or WINDOWING Function.

The data is :

Qty Rate Amount

10 100 1000
-5
10 100 1000
20 110 2200
-10

The final data should like this, just to give you an Idea.

Qty Rate Amount Cumulative Qty Avg.Rate Amount

10 100 1000 10 10 1000
-5 100 -500 5 100 500
10 100 1000 15 100 1500
20 110 2200 35 105.72 3700
-10 105.72 1050 25 105.72 2650

Best Regards,

Luqman
  • 1. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    94799 Explorer
    Currently Being Moderated
    How should we derive the order of the transactions?
  • 2. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    546595 Newbie
    Currently Being Moderated
    there in no criteria on which we can calculate,
    i just added ID column . just to give you an idea.
    Also i am not sure How are you calculating average .
    please let me know.

    try this
     1   with tab as
    2        (select 1 id,10 qty,    100 rate,     1000 amount  from dual
    3            union all
    4        select  2,-5,     100,     -500    from dual
    5           union all
    6       select  3,10,     100,     1000     from dual
    7          union all
    8       select 4,20,    110,       2200   from dual
    9          union all
    10        select 5,-10 ,    105.72,     1050 from dual)
    11         select qty,rate,amount,
    12                sum(qty) over(order by id)New_qty,
    13                sum(amount) over(order by id)commulitive
    14*       from tab
    QL> /

          QTY       RATE     AMOUNT    NEW_QTY COMMULITIVE
    --------- ---------- ---------- ---------- -----------
           10        100       1000         10        1000
           -5        100       -500          5         500
           10        100       1000         15        1500
           20        110       2200         35        3700
          -10     105.72       1050         25        4750
  • 3. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    ushitaki Newbie
    Currently Being Moderated
    I want to calculate the average cost of sales, so
    Qty Rate   Amount C_Qty C_Amount Avg.Rate
    10 100      1000    10     1000      100
    -5 100      -500     5      500      100
    10 100      1000    15     1500      100
    20 110      2200    35     3700      105.72
    -10 105.72   1050    25     2650      105.72
    (1) If (Qty < 0) then Rate := Previous Avg.Rate.
    (2) Amount := Qty * Rate
    (3) C_Amount := Previous C_Amount + Amount
    (4) C_Qty := Previous C_Qty + Qty
    (5) Avg.Rate := C_Amount / C_Qty

    This pseudo code is corrrect, is not?
  • 4. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    546595 Newbie
    Currently Being Moderated
    or may be this
    Wrote file afiedt.buf

      1    with tab as
      2        (select 1 id,10 qty,    100 rate,     1000 amount  from dual
      3            union all
      4        select  2,-5,     100,     -500    from dual
      5           union all
      6       select  3,10,     100,     1000     from dual
      7          union all
      8       select 4,20,    110,       2200   from dual
      9          union all
    10        select 5,-10 ,    105.72,     1050 from dual)
    11         select qty,rate,amount,
    12                sum(qty) over(order by id)New_qty,
    13                sum(amount) over(order by id)commulitive,
    14                round(sum(amount) over(order by id)/sum(qty) over(order by id),2)avg_rate
    15*         from tab
    16  /

           QTY       RATE     AMOUNT    NEW_QTY COMMULITIVE   AVG_RATE
    ---------- ---------- ---------- ---------- ----------- ----------
            10        100       1000         10        1000        100
            -5        100       -500          5         500        100
            10        100       1000         15        1500        100
            20        110       2200         35        3700     105.71
           -10     105.72       1050         25        4750        190
  • 5. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    162208 Newbie
    Currently Being Moderated
    Hi Ushitaki,

    You are very well correct, the pseudocode you mentioned is what exactly I want to do with the Sql, but how, I don't know.

    Best Regards,

    Luqman
  • 6. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    162208 Newbie
    Currently Being Moderated
    Hi Devmiral,

    As I mentioned before, I don't know the cost of Sales, I have to calculate this during the query according to the Purchase Costs.

    The formula is well defined by Utishaki in this thread.

    Any idea please ?

    Best Regards,

    Luqman
  • 7. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    MScallion Pro
    Currently Being Moderated
    As I mentioned before, I don't know the cost of Sales, I have to calculate this during the query according to the Purchase Costs.
    The formula is well defined by Utishaki in this thread.


    What if the quantity in the first row returned by your query is negative?

    Message was edited by:
    MScallion
  • 8. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    ushitaki Newbie
    Currently Being Moderated
    It's very difficult to get solution by only standard SQL with analytic function. Because, even analytic functions base on static set theory. We can refer some previous rows by LAG, and also we can accumulate some rows by SUM,COUNT etc., but each of they is an element of selected set. On the time of applying LAG,SUM,COUNT etc., each of referred rows need to be fixed value on the set.

    If there are unconfirmed value on selected set and need to refer other sets for confirming it, we need recursive approach on SQL. For this, I think that is something like as 'connect by'(Oracle) or 'recursive common view'(Sybase SQL Anywhere). If there was something like sys_connect_by_path analytic function, we are more happy on cost average calculating, material requirement planning and any more cases.

    I have ever written some accumulation program. We might be able to adapt it for this problem, or there are more easy solutions or only more complex solutions.
    -- This is my sample, but there are lots of same function posted on www sites. 
    create or replace function eval(in_str varchar) return number
    is
       p_ret number;
    begin
       execute immediate 'select '||in_str||' from dual ' into p_ret;
       return p_ret;
    end;
    /

    --
    column path  format a25
    column t_sal format a25
    -- This is only an example. There are no meanings in real company.
    select sys_connect_by_path(ename,'/') path,
           sys_connect_by_path(to_char(sal),'+') t_sal,
           eval(sys_connect_by_path(to_char(sal),'+')) eval_t_sal
    from scott.emp
    connect by prior empno = mgr
    start with ename='KING'
    ;


    PATH                      T_SAL                     EVAL_T_SAL
    ------------------------- ------------------------- ----------
    /KING                     +5000                           5000
    /KING/JONES               +5000+2975                      7975
    /KING/JONES/FORD          +5000+2975+3000                10975
    /KING/JONES/FORD/SMITH    +5000+2975+3000+800            11775
    /KING/BLAKE               +5000+2850                      7850
    /KING/BLAKE/ALLEN         +5000+2850+1600                 9450
    /KING/BLAKE/WARD          +5000+2850+1250                 9100
    /KING/BLAKE/MARTIN        +5000+2850+1250                 9100
    /KING/BLAKE/TURNER        +5000+2850+1500                 9350
    /KING/BLAKE/JAMES         +5000+2850+950                  8800
    /KING/CLARK               +5000+2450                      7450
    /KING/CLARK/MILLER        +5000+2450+1300                 8750
  • 9. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    RobvanWijk Oracle ACE
    Currently Being Moderated
    Luqman,

    If you are on 10g, then you could take advantage of the the model clause for this kind of calculations:
    SQL> create table mytable
      2  as
      3  select 1 your_ordering_column, 10 qty, 100 rate, 1000 amount from dual union all
      4  select 11, -5, null, null from dual union all
      5  select 13, 10, 100, 1000 from dual union all
      6  select 14, 20, 110, 2200 from dual union all
      7  select 87, -10, null, null from dual
      8  /

    Tabel is aangemaakt.

    SQL> select qty      "Qty"
      2       , rate     "Rate"
      3       , amount   "Amount"
      4       , c_qty    "Cumulative Qty"
      5       , avg_rate "Avg.Rate"
      6       , c_amount "Amount"
      7    from ( select t.*
      8                , count(*) over () - 1 number_of_iterations
      9                , row_number() over (order by your_ordering_column) dim
    10             from mytable t
    11         )
    12   model
    13         dimension by (dim)
    14         measures (qty, rate, amount, 0 as c_qty, 0 as avg_rate, 0 as c_amount, number_of_iterations)
    15         rules iterate (10000) until iteration_number > number_of_iterations[1]
    16         ( rate[iteration_number]     = case
    17                                        when iteration_number > 1 and qty[iteration_number] < 0 then avg_rate[iteration_number - 1]
    18                                        else rate[iteration_number]
    19                                        end
    20         , amount[iteration_number]   = qty[iteration_number] * rate[iteration_number]
    21         , c_amount[iteration_number] = case
    22                                        when iteration_number > 1 then c_amount[iteration_number - 1] + amount[iteration_number]
    23                                        else amount[iteration_number]
    24                                        end
    25         , c_qty[iteration_number]    = qty[iteration_number] +
    26                                        case
    27                                        when iteration_number > 1 then c_qty[iteration_number - 1]
    28                                        else 0
    29                                        end
    30         , avg_rate[iteration_number] = round(c_amount[iteration_number] / c_qty[iteration_number],2)
    31         )
    32  /

           Qty       Rate     Amount Cumulative Qty   Avg.Rate     Amount
    ---------- ---------- ---------- -------------- ---------- ----------
            10        100       1000             10        100       1000
            -5        100       -500              5        100        500
            10        100       1000             15        100       1500
            20        110       2200             35     105,71       3700
           -10     105,71    -1057,1             25     105,72     2642,9


    6 rijen zijn geselecteerd.
    As you see this is an outcome where intermediate results have not been rounded or truncated. You might want to add some rounding to have the precision you like.

    I don't see a way to do this using lag/lead. But maybe padders has some ingenious solution without using the model, like he had in [url http://forums.oracle.com/forums/thread.jspa?threadID=439299]this thread?

    Regards,
    Rob.
  • 10. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    ushitaki Newbie
    Currently Being Moderated
    I try to adapt.

    create table tab1
    (Qty  number
    ,Rate number
    ,Ent  date
    )
    ;

    insert into tab1 values ( 10, 100, trunc(sysdate)-15);
    insert into tab1 values ( -5,null, trunc(sysdate)-13);
    insert into tab1 values ( 10, 100, trunc(sysdate)-11);
    insert into tab1 values ( 20, 110, trunc(sysdate)- 8);
    insert into tab1 values (-10,null, trunc(sysdate)- 6);

    select Qty,Rate,c_Qty
       ,eval(expr0||expr1) c_Amount
       ,eval(expr0||expr1)/c_Qty "Avg.Rate"
    from (
       select Qty,Rate,c_Qty
         ,lpad('(',cnt_Deliv,'(') expr0
         ,sys_connect_by_path(
            decode(sign(Qty),-1,')*('||to_Char(c_Qty)||'/'||to_Char(c_Qty-Qty)||')'
                               ,'+'||to_char(Rate*Qty))
           ,' ') expr1
         ,rn
       from
         (select Qty,Rate
                ,sum(Qty) over (order by Ent) c_Qty
                ,count(case when Qty < 0 then 1 end) over (order by Ent) cnt_Deliv
                ,row_number() over (order by Ent) rn from tab1)
       start with rn=1
       connect by prior rn = rn-1
      )
    /

           QTY       RATE      C_QTY   C_AMOUNT   Avg.Rate
    ---------- ---------- ---------- ---------- ----------
            10        100         10       1000        100
            -5                     5        500        100
            10        100         15       1500        100
            20        110         35       3700 105.714286
           -10                    25 2642.85714 105.714286
  • 11. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    RobvanWijk Oracle ACE
    Currently Being Moderated
    Wow!

    Compared to that, the model clause looks very simple ...

    You only miss two rate values, but you've probably seen that already.

    Regards,
    Rob.
  • 12. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    ushitaki Newbie
    Currently Being Moderated
    Compared to that, the model clause looks very simple
    Yes, I'm impressed by your solution.
    You only miss two rate values, but you've probably
    seen that already.
    That can be solved by applying analysis function once more,
    but that makes more complex.
  • 13. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    162208 Newbie
    Currently Being Moderated
    Hi Rob,

    The model clause of 10g is really fantastic, and I thank you very much for giving me such a nice example.

    I will do appreciate if you please let me know, if it is possible to reset the quantities and amounts when there are more than one item

    for example: our table contains two item codes, after finishing the 1st item, the model clause should reset the cumulative totals of quantities, amounts and average rates.

    I tried using partition clause but I could not achieve this, any idea please ?

    Best Regards,

    Luqman
  • 14. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
    RobvanWijk Oracle ACE
    Currently Being Moderated
    I tried using partition clause but I could not
    achieve this, any idea please ?
    The partition clause should do this exactly.

    Did you change
     model
           dimension by (dim)
    to
     model
           partition by (item_code)
           dimension by (dim)
    ?

    If you did and you did not get the desired results, please post what you tried here and I'll have a look.

    Regards,
    Rob.

    Message was edited by:
    Rob van Wijk

    The above is only part of the solution. I now realize that more adjustments are needed:

    - the row_number analytic function must be partitioned by item_code as well
    - the iteration_number expression must now contain the "max of all partition" instead of "count of all"
    - In the model clause new rows should not be created, so some adjustment here has to take place as well.

    Hopefully I have some more time tomorrow and I'll change the example.
1 2 Previous Next