I have below data set

I have a dataset where I need to update the OPEN_BAL and CLOSE_BAL columns based on specific rules when the status is F. The rules are as follows:

The OPEN_BAL for the current day should be the CLOSE_BAL from the previous day.

The CLOSE_BAL for the current day should be OPEN_BAL + 1.

The subsequent OPEN_BAL and CLOSE_BAL should rely on the previously calculated values.

For example:

For Day 2, the OPEN_BAL is 158 (correct), and the CLOSE_BAL should be 159.

For Day 3, the OPEN_BAL should be 159, and the CLOSE_BAL should be 160.

For Day 4, the OPEN_BAL should be 160, and the CLOSE_BAL should be 161, and so on.

Here is the sample data and the expected output:

I’m trying to achieve this with the query below, but I’m encountering issues because the LAG function only works correctly when the values in the stock table are accurate. I realize that my current solution is incorrect, but I haven’t found a way to make the LAG function reference itself. Does anyone know if it’s possible to do this within a SELECT query?

    WITH adjusted_balances AS (
    SELECT 
        YEAR(TRY_TO_DATE(TRIM(f.open_date), 'yymmdd')) AS folio_yr,
        s.folio_mo, 
        s.folio_no,
        f.tt_status AS status,
        IFNULL(TRY_CAST(s.open_bal AS FLOAT), 0) AS open_bal,
        IFNULL(TRY_CAST(s.close_book AS FLOAT), 0) AS close_bal,
        LAG(IFNULL(TRY_CAST(s.close_book AS FLOAT), 0)) OVER (ORDER BY folio_yr, s.folio_mo, s.folio_no) AS prev_close_bal
    FROM 
        Stock s
    JOIN 
        FolioStatus f
    ON 
        s.folio_mo = f.fol_mo
        AND s.folio_no = f.fol_no
        AND s.term_id = f.term_id
        AND UPPER(TRIM(s.term_code)) = UPPER(TRIM(f.term_code))
    WHERE 
        s.supplier_no = '0000000000' 
        AND s.term_id = '00000BN' 
        AND folio_yr = '2024' 
        AND s.folio_mo = '08' 
        AND s.prod_id = 'MOBRED'
)
SELECT
    folio_yr, 
    folio_mo, 
    folio_no,
    status,
    open_bal,
    close_bal,
    CASE
        WHEN folio_no = 1 THEN open_bal
        ELSE COALESCE(prev_close_bal, open_bal)
    END AS expect_result_open,
    CASE
        WHEN folio_no = 1 THEN close_bal
        ELSE COALESCE(prev_close_bal, open_bal) + 1
    END AS expect_result_close
FROM
    adjusted_balances;