sql-server - Calculating Inventory running quantity/value & moving average unit cost

Question

Any help to the following problem I have will be appreciated, thank you. I am trying to perform some retrospective costings on stock inventory and failing to realise my results… I have tried various window functions (lead/Lag/row_number) etc along with multiple ctes and also First_Value….but have failed miserably….so hopefully your thoughts may get me where I want to be.

MS SQL SERVER 2019

This is what I have:

tId	tType	tcode	tDate	tQty	tValue
1	PO_IN	456	20210901	200	3654.00
2	SO_OUT	456	20210903	-155
3	SO_OUT	456	20210904	-15
4	PO_IN	456	20210905	150	3257.00
5	SO_OUT	456	20210906	-120
6	SO_OUT	456	20210907	-10
7	FIN_ADJ	456	20210908	0	-75.00
8	SO_OUT	456	20210909	-20
9	PO_IN	456	20210902	5	0.00
10	SO_OUT	456	20210910	-35

This is what I wish to achieve:

tId	tType	tcode	tDate	tQty	tValue	Run_Qty	Run_Value	avg_cost
1	PO_IN	456	20210901	200	3654.00	200	3654.00	18.27
2	SO_OUT	456	20210903	-155		45	822.15	18.27
3	SO_OUT	456	20210904	-15		30	548.10	18.27
4	PO_IN	456	20210905	150	3257.00	180	3805.10	21.14
5	SO_OUT	456	20210906	-120		60	1268.37	21.14
6	SO_OUT	456	20210907	-10		50	1056.97	21.14
7	FIN_ADJ	456	20210908	0	-75.00	50	981.97	19.64
8	SO_OUT	456	20210909	-20		30	589.18	19.64
9	PO_IN	456	20210902	5	0.00	35	589.18	16.83
10	SO_OUT	456	20210910	-35		0	0.00

PO_IN have a positive tqty and a positive or zero tvalue. This can be used to create/ adjust the moving avg cost per unit.

SO-OUT are negative tqty and have NULL tValue and decrement the running qty column and decrement the running value column by the tqty * the previous row avg cost

FIN_ADJ are tValue only and can be positive/negative.

the order of processing required is by tid and not tDate.

If I can get this to output the correct closing qty/value and the correct avg cost per row, I can then move onto the next step of analysis.

Thanks

EDIT....real world tables will have 100K+ rows

Some initial setup code

CREATE TABLE tst_Inv(
   tId    INT  NOT NULL
  ,tType  VARCHAR(7) NOT NULL
  ,tcode  INT  NOT NULL
  ,tDate  DATE  NOT NULL
  ,tQty   INTEGER  NOT NULL
  ,tValue NUMERIC(7,2)
);
INSERT INTO tst_Inv(tId,tType,tcode,tDate,tQty,tValue) VALUES
 (1,'PO_IN',456,'20210901',200,3654.00),(2,'SO_OUT',456,'20210903',-155,NULL),(3,'SO_OUT',456,'20210904',-15,NULL)
,(4,'PO_IN',456,'20210905',150,3257.00),(5,'SO_OUT',456,'20210906',-120,NULL),(6,'SO_OUT',456,'20210907',-10,NULL)
,(7,'FIN_ADJ',456,'20210908',0,-75.00),(8,'SO_OUT',456,'20210909',-20,NULL),(9,'PO_IN',456,'20210902',5,0.00)
,(10,'SO_OUT',456,'20210910',-35,NULL);

SELECT * FROM tst_Inv

标签： sql-servertsqlsql-server-2019

Answer 1

我正在使用 CTE

;WITH xQ
AS
(
    SELECT tId,tType, tcode, tDate, tQty, tValue , 
        tQty AS Run_Qty, 
        CAST (tValue AS NUMERIC(7,2)) AS Run_Value , 
        tValue / tQty AS Avg_Cost
    FROM tst_Inv
    WHERE tId = 1
    UNION ALL
    SELECT tId,tType, tcode, tDate, tQty, tValue ,
        xB.Run_Qty,
        xB.Run_Value,
        CASE WHEN xB.Run_Qty = 0 THEN NULL ELSE xB.Run_Value / xB.Run_Qty END AS Avg_Cost  
    FROM 
    (
        SELECT tId,tType, tcode, tDate, tQty, tValue,
            Run_Qty,            
            CAST(
                CASE xA.tType
                    WHEN 'PO_IN' THEN xA.tValue + xA.Run_Value
                    WHEN 'FIN_ADJ' THEN xA.tValue + xA.Run_Value
                    ELSE xA.Run_Qty * xA.Avg_Cost
                END
            AS NUMERIC(7,2)) AS Run_Value,
            xA.Avg_Cost 
        FROM
        (
            SELECT tst_Inv.* , 
                tst_Inv.tQty + xQ.Run_Qty AS Run_Qty,
                xQ.Run_Value,
                xQ.Avg_Cost
            FROM tst_Inv
            INNER JOIN xQ ON (tst_Inv.tId -1) = xQ.tId
            
        ) AS xA
    ) AS xB
)

SELECT * FROM xQ