1 2 Previous Next 17 Replies Latest reply on May 13, 2008 5:27 PM by ADFBeginer

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

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 ?
How should we derive the order of the transactions?
• 2. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
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 .

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 ?
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 ?
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 ?
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 ?
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.

Best Regards,

Luqman
• 7. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?
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 ?
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
;

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 ?
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.

Regards,
Rob.
• 10. Re: Using LAG / LEAD to calculate Inventory Cost and Running Average ?

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
,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)
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 ?
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 ?
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
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 ?
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 ?
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