sql-server - 计算库存日志的加权平均成本

Question

我必须计算我的产品库存成本，因此对于每次购买后的每种产品，我都必须重新计算加权平均成本。 在此处输入图像描述

我有一个观点，那就是每次进出后都会给我带来当前产品的库存：

StockID ArticleID TranDate TranCode Items Price WACPrice WACRunningTotal 62515 698 00:00,0 IN 484,05 280,56 484,05 89010 698 11:48,1 OUT 4 480,05 90155 698 39:26,8 OUT 3 477,05 9330 698 32:21,5 输入 400 302,1 877,05 89202 698 04:57,4 输出 1,5 875,55 87474 698 05:28,6 输出 4 871,55

我想计算 WACprice 字段

create 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 not in (1,5,6,10,20) 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

当我实现此代码时，我收到以下错误：

“消息 240，级别 16，状态 1，过程 sp_GetInventoryDe​​tails_ByWAC，第 9 行 [批处理开始行 0] 类型在递归查询“x”的列“rt”中的锚点和递归部分之间不匹配。” 请帮忙

标签： sql-serveraverageweighted