SQL Server TSQL Calculate Moving Average Unit Cost (MAUC)

tzdcorbm 于 2023-02-28 发布在其他

关注(0)|答案(2)|浏览(126)

I want to calculate the MAUC of items in the future. Items can be components or products created from those components. For every item I gathered a list of all planned supply (e.g. purchase orders) and demand transactions (e.g. sales orders) with their corresponding date. For supply I have a supply price and for demand I set the column to 0.

An example of the above described data can be seen in the columns Item_ID, Date, Quantity and Unit_Value (Below is the code). I simplified the date, because an int fulfills the same purpose as a date when it comes to ordering.

The other three columns (MAUC, Total and Stock) are the columns I want to calculate. Stock is easy with a running sum. The Total too is easy since it is MAUC*Stock=Total. Only the MAUC is difficult.

Why? Because I don't want to use a while loop over the table and insert every line one by one. Instead I am looking for a solution that uses over with aggregate and window functions. I believe that this would be way faster than a loop.

So my goal is something like this:

Select 
Item_ID
, Date
, Quantity
, Unit_Value
, sum(Unit_Value) 
    over (
    Partition by Item 
    Order by Date 
    Magic with between current row and unbound preceeding
) as MAUC
From Table

Do you know a solution that fulfills the following criteria:

Easy to read
As few lines as possible
Runs efficiently

The last one is the most important to me.

Here is the example code:

Create Table #test (
    Item_ID Int
    , Date Int
    , Quantity Bigint
    , Unit_Value Decimal(10,3) 
    , MAUC float
    , Total float
    , Stock Bigint
    )

Insert Into #test
values 
(1,1,10,100,100,1000,10)
, (1,2,-5,0,100,500,5)
, (1,3,-3,0,100,200,2)
, (1,4,20,120,118.181818181818,2600,22)
, (1,5,-5,0,118.181818181818,2009.09090909091,17)
, (1,6,-5,0,118.181818181818,1418.18181818182,12)
, (1,7,-5,0,118.181818181818,827.272727272727,7)
, (1,8,10,90,101.604278074866,1727.27272727273,17)
, (1,9,-2,0,101.604278074866,1524.06417112299,15)
, (1,10,-1,0,101.604278074866,1422.45989304813,14)
, (2,1,100,3,3,300,100)
, (2,2,-30,0,3,210,70)
, (2,3,-60,0,3,30,10)
, (2,4,300,2.5,2.51612903225806,780,310)
, (2,5,-100,0,2.51612903225806,528.387096774194,210)
, (2,6,-150,0,2.51612903225806,150.967741935484,60)
, (2,7,200,3.2,3.04218362282878,790.967741935484,260)
, (2,8,-30,0,3.04218362282878,699.70223325062,230)
, (2,9,-15,0,3.04218362282878,654.069478908189,215)
, (2,10,-70,0,3.04218362282878,441.116625310174,145)

sql-server

来源：https://stackoverflow.com/questions/75515830/tsql-calculate-moving-average-unit-cost-mauc

2条答案

按热度按时间

bvhaajcl1#

This is the solution using recursive cte

with
rcte as
(
    select item_ID, [Date], Quantity, Unit_Value, MAUC, Total, Stock,
           calc_mauc = convert(float, Unit_Value)
    from   #test
    where  [Date] = 1

    union all

    select t.item_ID, t.[Date], t.Quantity, t.Unit_Value, t.MAUC, t.Total, t.Stock,
           calc_mauc = case when t.Quantity > 0
                            then convert(float, ((t.Quantity * t.Unit_Value) + r.Total) 
                                              / nullif(r.Stock + t.Quantity, 0))
                            else r.calc_mauc
                            end
    from   #test t
           inner join rcte r on  t.item_ID = r.item_ID
                             and t.[Date]  = r.[Date] + 1
)
select *
from   rcte
order by item_ID, Date

db<>fiddle demo

Note : I get slight different result, you might want to verify the result and the formula for calculation

赞(0）回复(0）举报 2023-02-28

juud5qan2#

Ok, just go classic with the average:

Select 
  Item_ID
, Date
, Quantity
, Unit_Value
, sum(Unit_Value) over (Partition by Item_ID Order by Date rows between unbounded preceding and current row ) 
 /sum(Quantity)   over (Partition by Item_ID Order by Date rows between unbounded preceding and current row ) 
as MAUC
From #test

and since you like clarity, from version 2022 you can DRY the window:

Select 
  ...
, sum(Unit_Value) over wmauc /sum(Quantity)   over wmauc as MAUC
From #test
window wmauc as (Partition by Item_ID Order by Date rows between unbounded preceding and current row )

赞(0）回复(0）举报 2023-02-28

我来回答

SQL Server TSQL Calculate Moving Average Unit Cost (MAUC)

2条答案

相关问题

热门标签

最新问答