Retrieve values from rows other than the previous in a recursive CTE

Question

I am running a recursive CTE in order to calculate the average weighted cost of a product for x given warehouses. In this table, we can see a very simplified version of what the original data looks like:

Simplified original data

The first two rows are the initial values for the warehouses. That is why they have "N/A" in the Movement column. The AVG_Weighted_Price column is 0 for the remaining rows because that is the value I wish to calculate with the recursive cte.

I have created a recursive cte which intends to calculate the AVG_Weighted_Price column and it does so with the following simplified (and frankly wrong) formula -> (b.Movement * a.AVG_Weighted_Price)/b.Total_Quantity (Having a as the previous row and b as the row being calculated).

In the table, it is clear this will not work because I have to retrieve the most recent value from the same Warehouse, which is not always the previous row. This could be solved simply by using the first two values as anchors and running the recursive cte for the A warehouse parent first, and later for the B warehouse parent. However, because the AVG_Weighted_Price in one warehouse will affect the other, I have to run the recursion using the field "ID" as the order since it represents the order in which the movements (rows) happened. Nonetheless, the initial values (row 1 and 2) will pass with their original values and will not undergo any calculations (row 1 because it is the anchor and row 2 because it will be hardcoded to do so).

If I could run the recursion in the order of the warehouses and not necessarily in the order of the ID, the following query would be correct (#Sample_Table is the table showed in the picture above):

DROP TABLE IF EXISTS #RS

;WITH cte
AS
(
SELECT *
FROM #Sample_Table
WHERE Warehouse_Order = 1

UNION ALL

SELECT b.Warehouse
      ,b.Movement
      ,b.Total_Quantity
      ,CASE WHEN b.Warehouse_Order = 1 THEN b.AVG_Weighted_Price
            ELSE (b.Movement * a.AVG_Weighted_Price) / b.Total_Quantity END AS AVG_Weighted_Price
      ,b.ID
      ,b.Warehouse_Order
FROM cte a
INNER JOIN #Sample_Table b
ON b.Warehouse = a.Warehouse AND b.Warehouse_Order = a.Warehouse_Order + 1 
)

SELECT *
INTO #RS
FROM cte

This would be the result of this query:

Result from first query

This, however, is incorrect because, as I said before, the recursion must run in the same order as the ID. For this reason, I tried to apply a LAG that retrieves the most recent value from the same warehouse. However, as far as I am aware, LAG doesn't work on recursive cte's and it always returns a NULL value. Here is the code I tried to use (note the changes in the Anchor WHERE clause and in the JOIN conditions, as well as the LAG present in the calculated field):

DROP TABLE IF EXISTS #RS

;WITH cte
AS
(
SELECT *
FROM #Sample_Table
WHERE ID = 1

UNION ALL

SELECT b.Warehouse
      ,b.Movement
      ,b.Total_Quantity
      ,CASE WHEN b.Warehouse_Order =1 THEN b.AVG_Weighted_Price
            ELSE (b.Movement * LAG(b.AVG_Weighted_Price) OVER (PARTITION BY b.Warehouse ORDER BY b.ID)) / b.Total_Quantity END AS AVG_Weighted_Price
      ,b.ID
      ,b.Warehouse_Order
FROM cte a
INNER JOIN #Sample_Table b
ON b.ID = a.ID + 1
)

SELECT *
INTO #RS
FROM cte

The result of this query is as follows:

Result from second query

I understand why the LAG returns the NULL values and why we cannot use it here, but I honestly can't seem to find another solution. The original data has tens of centers and millions of rows, so a WHILE loop to treat these cases one by one would be too consuming (already tested).

If anyone could help me solve this issue, I would forever be thankful as I have been banging my head on this problem for quite some time now. Thank you for your patience and sorry if I was, at anytime, confusing.

Edit: I created an Excel in order to better clarify the issue. I hope this helps:

user_7706381 · Answer

This looks like it is a fairly simple problem. However, your question is difficult to understand.

Here is what I would want:

a SQL script to recreate a representative sample of your data. like this

 declare @test table
 (Warehouse  varchar(20)
 ,Movement   decimal (19,2)
 , Total_Quantity decimal (19,2)
 , Avg_Weighted_Price decimal (19,2)
 , ID int
 , Warehouse_Order int)
 insert @test
 values
 (   'A',    null,   '100',  '10',   '1',    '1')
 ,(  'B',    null,   '30',   '5',    '2',    '1')
 ,(  'A',    '10',   '110',  '0',    '3',    '2')
 ,(  'A',    '-5',   '105',  '0',    '4',    '3')
 ,(  'B',    '30',   '60',   '0',    '5',    '2')
 ,(  'B',    '5',    '65',   '0',    '6',    '3')
 ,(  'B',    '-25',  '40',   '0',    '7',    '4')
 ,(  'A',    '10',   '115',  '0',    '8',    '4')
 ,(  'B',    '10',   '50',   '0',    '9',    '5')
 ,(  'A',    '10',   '125',  '0',    '10',   '5')

 SELECT * FROM @test

Description of your data so far, as I understand the starting value or opening balance of inventory in a warehouse can be seen in rows that have a null value for Movement. Movement: +ve values are additons/recipts of item; -ves are reductions Total_quantity shows current position (opening balance + movement)
what you are trying to do with this data. as i understand it, update each row with compute avg_weighted_price

How is your average weighted price determined?
I think i understand what you are trying to do with lag and am fairly certain that your approach is wrong. (first clue: There is no cost associated with each receipt)

Try your formula in a simpler way - use excel or paper and pen and manually calculate the Avg_Weighted_price. that might clarify things a bit
Try explaining the purpose of this exercise. Why do you need this avg_weighted_price on every row?

when the problem is well defined, i expect the solution will be fairly simple.

Edit1; responding to excel sample: Lag will work only once for you, as you can see here:

        SELECT * 
    , lag(Avg_Weighted_Price, 1, 0) over (partition by Warehouse order by Warehouse_Order, id) as Lagprice
    , case when Movement is null then Avg_Weighted_Price
           when Total_Quantity <> 0 
           then Movement * lag(Avg_Weighted_Price, 1, 0) over (partition by Warehouse order by Warehouse_Order, id)/Total_Quantity 
           else 0 end as ComputedAvgPrice
    From @test order by ID

Notice that the Aug_Weighted_price falls from 10 to 0.909 Is that the result you want?

Retrieve values from rows other than the previous in a recursive CTE

1 Answers