All geek questions in one place

Calculation of the weighted average cost of production

I need to calculate the cost of my products, so for each product after each purchase I will have to recalculate the Weighted Average Cost .

I got a view that brings me the current product after each entry / exit:

document_type   document_date   product_id  qty_out qty_in  price       row_num stock_balance
SI              01/01/2014      52          0       600     1037.28     1           600
SI              01/01/2014      53          0       300     1357.38     2           300
LC              03/02/2014      53          100     0       1354.16     3           200
LC              03/02/2014      53          150     0       1355.25     4           50
LC              03/02/2014      52          100     0       1035.26     5           500
LC              03/02/2014      52          200     0       1035.04     6           300
LF              03/02/2014      53          0       1040    1356.44     7           1090
LF              03/02/2014      52          0       1560    1045        8           1860
LC              04/02/2014      52          120     0       1039.08     9           1740
LC              04/02/2014      53          100     0       1358.95     10          990
LF              04/02/2014      52          0       600     1038.71     11          2340
LF              04/02/2014      53          0       1040    1363.3      12          2030
LC              05/02/2014      52          100     0       1037.78     13          2240
LF              15/03/2014      53          0       20      1365.87     14          2050
LF              15/03/2014      52          0       50      1054.19     15          2290

I want to add a computed field WACas above:

document_type   document_date   product_id  qty_out qty_in  price           row_num     stock_balance   WAC 
SI              01/01/2014      52          0       600     1 037,28        1           600             1037,28000000000
SI              01/01/2014      53          0       300     1 357,38        2           300             1357,38000000000
LC              03/02/2014      53          100     0       1 354,16        3           200             1357,38000000000
LC              03/02/2014      53          150     0       1 355,25        4           50              1357,38000000000
LC              03/02/2014      52          100     0       1 035,26        5           500             1037,28000000000
LC              03/02/2014      52          200     0       1 035,04        6           300             1037,28000000000
LF              03/02/2014      53          0       1040    1 356,44        7           1090            1356,48311926606 --((1357,38*50)+(1040*1356,44))/(1090)
LF              03/02/2014      52          0       1560    1 045,00        8           1860            1043,75483870968 --((1037,28*300)+(1560*1045))/(1860)
LC              04/02/2014      52          120     0       1 039,08        9           1740            1043,75483870968
LC              04/02/2014      53          100     0       1 358,95        10          990             1356,48311926606
LF              04/02/2014      52          0       600     1 038,71        11          2340            1042,46129032258 --((1043,75483870968*1740)+(600*1038,71))/(2340)
LF              04/02/2014      53          0       1040    1 363,30        12          2030            1359,97000000000 --((1356,48311926606*990)+(1040*1363,3))/(2030)
LC              05/02/2014      52          100     0       1 037,78        13          2240            1042,46129032258
LF              15/03/2014      53          0       20      1 365,87        14          2050            1360,03301857239 --((1359,97551136621*2030)+(20*1365,87))/2050
LF              15/03/2014      52          0       50      1 054,19        15          2290            1042.71737568672 --((1042.46129032258*2240)+(50*1054.19))/2290

For each product, there is only one and only one type of document 'SI'(initial stock), and the price associated with it is equal initial WAC.

Here is an example of SQL Fiddle .

If anyone can help with this, I cannot figure it out.

Change . I just updated the calculated numbers, increasing the accuracy, showing 9 numbers after the decimal point.

+5
6

CTE:

SQLFiddle

with recursive
stock_temp as (
  select 
    *, 
    row_number() over(partition by product_id order by row_num) as rn
  from 
    stock_table 
)

,cte as (
  select 
    document_type, document_date, 
    product_id, qty_out, qty_in, price, 
    row_num, stock_balance, rn, 
    price as wac
  from 
    stock_temp where document_type = 'SI'

  union all

  select 
    sub.document_type, sub.document_date,
    sub.product_id, sub.qty_out,  sub.qty_in, sub.price,
    sub.row_num, sub.stock_balance,  sub.rn,
    case when sub.qty_in = 0 then main.wac else 
    ((sub.stock_balance - sub.qty_in) * main.wac + sub.qty_in * sub.price) 
      / ((sub.stock_balance - sub.qty_in)  + sub.qty_in) end as wac
  from 
    cte as main
    join stock_temp as sub 
      on (main.product_id = sub.product_id and main.rn + 1 = sub.rn)
)

select * from cte
+2

! - first/last/nth_value, Postgresql : This is likely to give unhelpful results for nth_value and particularly last_value.

. :

  • WAC , qty_in = 0 ( )
  • WAC WAC, "" , WAC, .

SQLFiddle

0

:

CREATE TYPE stock_table_with_wac AS
   (document_type character varying,
    document_date date,
    product_id bigint,
    qty_out double precision,
    qty_in double precision,
    price double precision,
    row_num bigint,
    stock_balance double precision,
    wac double precision);


CREATE OR REPLACE FUNCTION calculate_wac_value()
  RETURNS SETOF stock_table_with_wac AS
$BODY$
    DECLARE
    r_article stock_table_with_wac%rowtype;--maintain the liste of all products with there wac value
    r_in_out_article stock_table_with_wac%rowtype;--Each other records
    BEGIN
    --For each products
    FOR r_article IN SELECT *, price FROM stock_table where document_type='SI' order by row_num
        LOOP
        return next r_article; 
        FOR r_in_out_article IN SELECT * FROM stock_table where document_type<>'SI' and product_id=r_article.product_id order by row_num
        LOOP
        --If there is an entry calculate the wac
        if r_in_out_article.qty_in >0 then 
            r_in_out_article.wac:=((r_article.price * (r_in_out_article.stock_balance - r_in_out_article.qty_in)) + (r_in_out_article.qty_in * r_in_out_article.price))/(r_in_out_article.stock_balance);       
            --Update the wac value of the product
            r_article.price:= r_in_out_article.wac;
        else --The waca value still inchanged:      
            r_in_out_article.wac:= r_article.price;
        end if;     
            RETURN NEXT r_in_out_article; -- return current row with caluculated wac if any
        END LOOP;
        return next r_article;
        END LOOP;
        RETURN;
    END
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION calculate_wac_value()
  OWNER TO postgres;

select * from calculate_wac_value();

, . ?

0

C PostgreSQL, , , , SQL:

https://github.com/Kozea/weighted_mean

0

. WAC . , , , . . :

USE [EUL]
GO

WITH Inventory_Temp AS (
  SELECT
    *,
    row_number() OVER (PARTITION BY ProductCode ORDER BY ID ASC) AS rn
  FROM
    InventoryTrx
),

cte AS (
  SELECT
    ProductCode,
    [Date],
    Units,
    CumUnits,
    Cost,
    [Type],
    rn,
    ID,
    Cost as wac
  FROM
    Inventory_Temp
  WHERE [Type] = 'p'

  UNION ALL

  SELECT
    sub.ProductCode,
    sub.[Date],
    sub.Units,
    sub.CumUnits,
    sub.Cost,
    sub.[Type],
    sub.rn,
    sub.ID,
    CASE WHEN sub.[Type] <> 'p' THEN main.wac ELSE
    ((sub.CumUnits - ABS(sub.Units)) * main.Cost + ABS(sub.Units) * sub.Cost) / sub.CumUnits END AS wac
  FROM cte AS main
    inner join Inventory_Temp AS sub
      ON (main.ProductCode = sub.ProductCode AND
          main.rn + 1 = sub.rn)
)

SELECT * FROM cte ORDER BY [DATE] ASC

.

-1

, , FIFO.

https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

, .

:

ID  ArticleID   TranDate    TranCode    Items   Price   WACPrice    WACRunningTotal
1   11782   2009-01-01 00:00:41.000 IN  809 256.82  256.82  NULL
2   16967   2009-01-01 00:00:50.000 IN  372 134.44  134.44  NULL
3   13078   2009-01-01 00:01:21.000 IN  532 201.69  201.69  NULL
4   10918   2009-01-01 00:01:34.000 IN  717 348.79  348.79  NULL
5   18871   2009-01-01 00:01:34.000 IN  1045    88.25   88.25   NULL
6   22379   2009-01-01 00:03:01.000 IN  401 326.59  326.59  NULL
7   24049   2009-01-01 00:03:24.000 IN  222 54.54   54.54   NULL
8   12570   2009-01-01 00:03:33.000 IN  731 29.25   29.25   NULL
9   10327   2009-01-01 00:03:33.000 IN  407 222.69  222.69  NULL
10  21548   2009-01-01 00:03:49.000 IN  400 254.05  254.05  NULL
11  15155   2009-01-01 00:03:51.000 IN  719 320.02  320.02  NULL
12  22706   2009-01-01 00:04:00.000 IN  331 25.91   25.91   NULL
13  19126   2009-01-01 00:04:16.000 IN  289 305.47  305.47  NULL
14  21722   2009-01-01 00:04:39.000 IN  434 3.80    3.80    NULL
15  20811   2009-01-01 00:05:57.000 IN  1043    316.57  316.57  NULL
16  21998   2009-01-01 00:06:01.000 IN  1009    15.18   15.18   NULL
17  12928   2009-01-01 00:06:45.000 IN  1122    265.71  265.71  NULL
18  14150   2009-01-01 00:07:36.000 IN  730 148.91  148.91  NULL
19  22307   2009-01-01 00:08:09.000 IN  986 184.38  184.38  NULL
20  17472   2009-01-01 00:08:34.000 IN  1182    62.73   62.73   NULL

, ddl .

I use Cte, but for 10,00001 records, it maximizes the maxrecursion counter. Thus, I created a procedure to execute one element identifier, which can be repeated by another procedure.

I added two columns to the stock table, WACPrice and WACRunningTotal.

please find my code below:

    alter proc sp_GetInventoryDetails_ByWAC
-- sp_GetInventoryDetails_ByWAC 10017
@ArticleId int
as
begin

-- select * from #stocktemp order by trandate 
--select *, 0 as WACRunningTotal into #StockTemp from stock where articleid=10000 order by TranDate;
;WITH y AS 
(
  SELECT articleid,TranDate,trancode, items, rn = ROW_NUMBER() OVER (ORDER BY TranDate)
    FROM stock where ArticleID =@ArticleId
), x AS
(
    SELECT articleid, TranDate,trancode, rn, items, rt = items
      FROM y
      WHERE rn = 1
    UNION ALL
    SELECT y.articleid,y.TranDate,y.trancode, y.rn, y.items,case when y.TranCode='OUT' then x.rt - y.items else x.rt+y.Items end
      FROM x INNER JOIN y
      ON y.rn = x.rn + 1
)

update st set st.WACRunningTotal=x.rt
from stock st
inner join x on x.ArticleID=st.ArticleID and x.TranDate=st.TranDate and x.TranCode=st.TranCode and isnull(st.WACRunningTotal,0)=0

OPTION (MAXRECURSION 0);




;WITH    StockCTE
          AS (SELECT    articleid,
                        items,
                        WACRunningTotal,
                        WACPrice,
                        trandate,
                        ROW_NUMBER() OVER (PARTITION BY articleid ORDER BY trandate) RowNum
              FROM      stock where ArticleID =@ArticleId),

/* CleanStock - A recursive CTE. This runs down the list of values for each stock, checking the Price column, if it is null it gets the previous non NULL value.*/
        CleanStock
          AS (SELECT    articleid,
                        items,
                        WACRunningTotal,
                        ISNULL(WACPrice ,0) WACPrice ,/* Ensure we start with no NULL values for each stock */
                        trandate,
                        RowNum
              FROM      StockCTE cur
              WHERE     RowNum = 1
              UNION ALL
              SELECT    Curr.articleid,
                        curr.items,
                        Curr.WACRunningTotal,
                        case when Curr.WACPrice=0 then  prev.WACPrice else ((curr.WACPrice*curr.Items)+(prev.WACRunningTotal*prev.WACPrice))/curr.WACRunningTotal end as WACPrice,
                        Curr.trandate,
                        Curr.RowNum
              FROM      StockCTE curr
              INNER JOIN CleanStock prev ON curr.articleid = prev.articleid
                                           AND curr.RowNum = prev.RowNum + 1)

/* Update the base table using the result set from the recursive CTE */
    UPDATE trg
    SET trg.WACPrice = src.WACPrice
    FROM    stock trg
    INNER JOIN CleanStock src ON trg.articleid = src.articleid
                                AND trg.trandate = src.trandate and trg.ArticleID=@ArticleId 

/* Display the results */
SELECT * FROM stock where ArticleID=@ArticleId order by TranDate

--drop table stock



end

This will give the result as

StockID ArticleID   TranDate    TranCode    Items   Price   WACPrice    WACRunningTotal
20119   10017   2009-01-06 10:37:40.000 IN  1088    27.91   27.91   1088
69802   10017   2009-01-20 00:40:44.000 OUT 39  0.00    27.91   1049
71338   10017   2009-01-20 10:42:25.000 OUT 967 0.00    27.91   82
91638   10017   2009-01-25 21:54:14.000 OUT 75  0.00    27.91   7
130881  10017   2009-02-05 14:38:34.000 IN  1061    312.18  310.3168    1068
174059  10017   2009-02-17 09:00:34.000 OUT 779 0.00    310.3168    289
188516  10017   2009-02-21 06:46:01.000 OUT 264 0.00    310.3168    25
192423  10017   2009-02-22 08:53:40.000 RET 8   0.00    310.3168    33
228070  10017   2009-03-04 02:24:26.000 OUT 30  0.00    310.3168    3
235671  10017   2009-03-06 03:59:49.000 IN  750 9.78    10.9773 753
247309  10017   2009-03-09 08:36:20.000 OUT 44  0.00    10.9773 709
249207  10017   2009-03-09 21:38:26.000 IN  256 155.09  49.2082 965
253665  10017   2009-03-11 02:42:14.000 OUT 379 0.00    49.2082 586
254121  10017   2009-03-11 05:59:00.000 OUT 527 0.00    49.2082 59
263772  10017   2009-03-13 20:46:09.000 OUT 11  0.00    49.2082 48
271272  10017   2009-03-15 22:09:04.000 OUT 34  0.00    49.2082 14
273709  10017   2009-03-16 13:51:34.000 OUT 1   0.00    49.2082 13
274065  10017   2009-03-16 16:13:02.000 OUT 5   0.00    49.2082 8
275679  10017   2009-03-17 02:38:20.000 IN  1165    217.19  216.0443    1173
280661  10017   2009-03-18 10:59:20.000 OUT 1053    0.00    216.0443    120

I am currently working to find COGS.

-2
source

Source: https://habr.com/ru/post/1531940/

More articles:


All Articles