Home » SQL & PL/SQL » SQL & PL/SQL » weighted average or moving average calculation for stock (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod)
| weighted average or moving average calculation for stock [message #604847] |
Sat, 04 January 2014 11:53  |
 |
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
|
Senior Member |
|
|
Dear Experts
i need to calculate the weighted average price for purchase price.
following link may helpful for me but i don't understand how can i use it for
solving my problem.
http://www.orafaq.com/forum/m/449491/?srch=weighted+average#msg_449491
Following is the formula will use for weighted average price calculation but i don't
understand how can we do it.
Average Unit Price :=
Q(B) * UP(B) + Q(t) * UP(T)
------------------------------------ (divided by)
Q(B) + Q(t)
Q(B) -> Quantity On hand Before Transaction
UP(B) -> Unit Price Before Transaction
Q(t) -> Transaction Quantity
UP(T) -> Transaction Unit Price
assume following is the table
OUTPUT using following formula in excel with format =((H10*I10)+(E11*F11))/(H10+E11)
B C E F G H I J
Item Code: 100001
Date Particulars Movement Balance
Qty Rate Amount Qty Rate Amount
9 01/01/2013 OP BAL 0.00 0.00 0.00
10 02/01/2013 GRN 10.00 17.00 170.00 10.00 17.00 170.00
11 03/01/2013 GRN 15.00 20.00 300.00 25.00 18.80 470.00
12 04/01/2013 SIR 12.00- 18.80 225.60- 13.00 18.80 244.40
GRN :> Goods Receipt Note
OP Bal:> OPening Balance
SIR :> Stock Issue
In Movement Head (Qty, Rate, Amount) GRN represents Transaction Stock In with Purchase Rate but SIR
with Calculated Avg Rate as per above given formula .
In Balance Head (Qty, Rate, Amount) Qty Represent Closing Calculated Qty(e.g 10+15 = 25,
25-12 = 13) or (On hand Before transaction), Rate Represent Avg Rate of Closing Qty, and Amount is ClosingQty * AvgRate
now i have tried a lot to make query that give the above result but in vain.
thanx
regards
|
|
|
|
|
|
| Re: weighted average or moving average calculation for stock [message #604859 is a reply to message #604852] |
Sat, 04 January 2014 15:17  |
|
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
|
Senior Member |
|
|
Dear
following the the table and data script with item code,date, qty, rate and amount
of current stock of same item code with purchase rate.
Running closing stock, avg rate, and amount will be calculated
using weighted average rate method.
CREATE TABLE STOCK1
(
GRN_DATE DATE,
ITEM_CODE VARCHAR2(10 BYTE),
QTY NUMBER,
RATE NUMBER,
AMOUNT NUMBER
)
INSERT INTO STOCK1 ( GRN_DATE, ITEM_CODE, QTY, RATE, AMOUNT ) VALUES (
TO_Date( '12/19/2013 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '11002001', 8740, 251
, 2193740);
INSERT INTO STOCK1 ( GRN_DATE, ITEM_CODE, QTY, RATE, AMOUNT ) VALUES (
TO_Date( '12/20/2013 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '11002001', 8540, 245
, 2092300);
INSERT INTO STOCK1 ( GRN_DATE, ITEM_CODE, QTY, RATE, AMOUNT ) VALUES (
TO_Date( '12/20/2013 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '11002001', 7450, 254
, 1892300);
INSERT INTO STOCK1 ( GRN_DATE, ITEM_CODE, QTY, RATE, AMOUNT ) VALUES (
TO_Date( '12/25/2013 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '11002001', 8260, 275
, 2271500);
INSERT INTO STOCK1 ( GRN_DATE, ITEM_CODE, QTY, RATE, AMOUNT ) VALUES (
TO_Date( '01/01/2014 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '11002001', 10180, 251
, 2555180);
INSERT INTO STOCK1 ( GRN_DATE, ITEM_CODE, QTY, RATE, AMOUNT ) VALUES (
TO_Date( '12/20/2013 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '11002001', 4320, 254
, 1097280);
COMMIT;
thanx
|
|
|
|
| Re: weighted average or moving average calculation for stock [message #605329 is a reply to message #604852] |
Wed, 08 January 2014 13:54 |
|
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
|
Senior Member |
|
|
Dear Experts
I have found some solution. wants to share with you.
first create table, insert rows and then run the following given procedure in 10g
calculated the moving weighted avg rate.
Create Table Test (
Dated date,
Units number,
Cost number);
insert into test
values (to_date('01-05-2012','dd-mm-rrrr') ,700,10);
insert into test
values ( to_date('03-05-2012','dd-mm-rrrr'), 100, 12 );
insert into test
values ( to_date('08-05-2012','dd-mm-rrrr'), -500, 00 );
insert into test
values ( to_date('15-05-2012','dd-mm-rrrr'), 600, 14 );
insert into test
values ( to_date('19-05-2012','dd-mm-rrrr'), 200, 15 );
insert into test
values ( to_date('25-05-2012','dd-mm-rrrr'), -400, 00 );
insert into test
values ( to_date('27-05-2012','dd-mm-rrrr'), -100, 00 );
SQL> select * from test;
WITH got_grp_id AS
(
SELECT dated, units, cost
, COUNT ( CASE
WHEN units > 0
THEN 1
END
) OVER (ORDER BY dated DESC) AS grp_id
FROM test
)
, got_rr AS
(
SELECT grp_id
, MAX (cost) AS cost
, SUM (units) AS inventory
, SUM (SUM (units)) OVER (ORDER BY grp_id DESC) AS run_sum
, MAX (units)
/ SUM (SUM (units)) OVER (ORDER BY grp_id DESC) AS rr
FROM got_grp_id
GROUP BY grp_id
)
, got_pct AS
(
SELECT p.grp_id
, c.grp_id AS child_grp_id
, c.cost
, c.rr
, NVL ( EXP ( SUM ( CASE
WHEN c.rr = 1
THEN 0
ELSE LN (1 - c.rr)
END
) OVER ( PARTITION BY p.grp_id
ORDER BY c.grp_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING
)
)
, 1
) * c.rr AS pct
FROM got_rr p
JOIN got_rr c ON c.grp_id >= p.grp_id
)
, got_avg_cost AS
(
SELECT grp_id
, SUM (cost * pct) AS avg_cost
FROM got_pct
GROUP BY grp_id
)
SELECT g.dated
, g.units
, g.cost
, CASE
WHEN g.units > 0
THEN a.avg_cost
ELSE p.avg_cost
END AS avg_cost
, SUM (g.units) OVER (ORDER BY g.dated) AS inventory
FROM got_grp_id g
JOIN got_avg_cost a ON a.grp_id = g.grp_id
LEFT OUTER JOIN got_avg_cost p ON p.grp_id = g.grp_id + 1
ORDER BY g.dated
;
regards
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 10:11:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
