Search code examples
sqlrecursion

SQL recursive formula using it's own result from previous row

I googled my mind out of this question, yet I can't find possible solution to this issue. I've a model, which is used to calculate forecast purchase (let's say from distributor to retail store) and forecast stock.

You have this input data set:

enter image description here

The formula for stock 2023 12 and further is:

iif(offtake_current_month + (offtake_current_month + offtake_next_month)/2 * mos_stock - stock_previous_month > 0, 0, offtake_current_month + (offtake_current_month + offtake_next_month)/2 * mos_stock - stock_previous_month)

The formula for purchase 2023 12 is:

stock_previous_month + stock_current_month - offtake_current month

The formula for purchase 2024 01 and further is:

purchase_previous_month + stock_current_month - offtake_current_month

Yellow and white cells represent existing data for the moment at which green cells are calculated

What i'm looking for:

Given the above formulas, you can see that each next month is referencing stock from previous month, which is the result of the formula from previous row. So for calculating stock for 2024 01 I need the result of the formula for stock from 2023 12. But how do I access this value, is the main issue.

Overall my main question is - how can you access result of formula from previous row and use it as part of calculation of the same formula for the current row

Thank you!

Solution

  • I might have found solution using cte. For now it's calculating correctly

    cte1 
AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY chain_code ORDER BY Year, Month) AS rn
    FROM j
), rcte AS (
    SELECT *, iif(stock_property = 'second fc calculated stock', stock_calculated_for_forecast, 0) AS stock_f
    FROM cte1 AS base
    WHERE rn = 1
    UNION ALL
    SELECT curr.*
            ,prev.stock_f + iif(prev.stock_f - curr.oftk_cur - (curr.oftk_cur + curr.oftk_fw) / 2 * curr.mos_stock > 0, 0, curr.oftk_cur + (curr.oftk_cur + curr.oftk_fw) / 2 * curr.mos_stock - prev.stock_f) - curr.oftk_cur
    FROM cte1 AS curr
    JOIN rcte AS prev ON curr.chain_code = prev.chain_code AND curr.rn = prev.rn + 1
)