How can I generate recursive rows for my table using LAG?

Question

Sample data and current attempt: https://dbfiddle.uk/A6zVd_Qy

I've got a table that looks like this:

CREATE TABLE base_table
(
  FUND_CODE varchar(255),
  OPENING_BALANCE float,
  TRANSACTION_DATE datetime,
  UNITS_ALLOCATED float,
  CLOSING_BALANCE float,
  ROW_ID int
  )

INSERT INTO base_table
VALUES 
  ('A', 10000, '20230530', 300, 10300, 1),
  ('A', 10000, '20230531', 350, 10350, 2),
  ('A', 10000, '20230601', -150, 9850, 3),
  ('A', 10000, '20230605', -200, 9800, 4),
  ('A', 10000, '20230615', -300, 9700, 5),
  ('A', 10000, '20230620', 200, 10200, 6)

In the above table, the first opening balance in the first row is the starting point. The units allocated are also correct figures as they come from other tables/ aggregate functions.

I would like the closing balance of the first row, and thereafter the opening and closing balances of each row to be calculated in SQL. The dates are sequential, although there could be gaps between two transaction dates (see dates for ROW_ID 3 and 4 as an example).

Here's what I've tried.

SELECT DISTINCT

  bt.FUND_CODE,
  CASE
    WHEN bt.ROW_ID = 1 THEN bt.OPENING_BALANCE
    ELSE (LAG(bt.CLOSING_BALANCE, 1, 0) OVER (PARTITION BY bt.FUND_CODE ORDER BY bt.TRANSACTION_DATE))
  END
     AS OPENING_BALANCE,
  bt.TRANSACTION_DATE,
  bt.UNITS_ALLOCATED,


  (CASE
    WHEN bt.ROW_ID = 1 THEN bt.OPENING_BALANCE
    ELSE (LAG(bt.CLOSING_BALANCE, 1, 0) OVER (PARTITION BY bt.FUND_CODE ORDER BY bt.TRANSACTION_DATE))
  END) + bt.UNITS_ALLOCATED
     AS CLOSING_BALANCE,

  bt.ROW_ID

FROM base_table bt ORDER BY bt.ROW_ID ASC

However, this yields:

In other words, the opening balance is wrong from the third row onwards, which renders everything else incorrect thereafter. The opening balance to be picked up should be 10,650, and the closing balance should be 10,500 for that row, and so on....

Can anyone please help out?

Desired end-result:

Answer 1

If I understand the expected results correctly, you may simply rearrange the calculations:

SELECT 
  FUND_CODE,
  OPENING_BALANCE = CASE 
    WHEN ROW_ID = 1 THEN OPENING_BALANCE
    ELSE LAG(CLOSING_BALANCE) OVER (PARTITION BY FUND_CODE ORDER BY TRANSACTION_DATE)
  END,
  TRANSACTION_DATE,
  UNITS_ALLOCATED,
  CLOSING_BALANCE,
  ROW_ID 
FROM (
  SELECT 
    FUND_CODE,
    OPENING_BALANCE,
    TRANSACTION_DATE,
    UNITS_ALLOCATED,
    CLOSING_BALANCE = OPENING_BALANCE + SUM(UNITS_ALLOCATED) OVER (PARTITION BY FUND_CODE ORDER BY TRANSACTION_DATE),
    ROW_ID 
  FROM base_table
) t