[SQL] Convert table data into a ledger format using PostgreSQL query

I have the following data in a PostgreSQL table:

id amount opening_amount closing_amount
1 200 1000 1200
2 -500 NULL NULL
3 -200 NULL NULL
4 1000 NULL NULL

I want to convert this data into a ledger format, calculating the opening_amount and closing_amount based on the previous row's closing_amount and the amount.

The expected result is:

id amount opening_amount closing_amount
1 200 1000 1200
2 -500 1200 700
3 -200 700 500
4 1000 500 1500

I tried using the LAG() function but it gives the wrong result:

id amount opening_amount closing_amount
1 200 1000 1200
2 -500 1200 700
3 -200 1000 800
4 1000 1000 2000

Here is the query I tried:

WITH ledger_data AS (
SELECT
id,
amount,
COALESCE(LAG(closing_amount) OVER (ORDER BY id), 1000) AS opening_amount, -- Assuming first record's opening balance is 1000
amount + COALESCE(LAG(closing_amount) OVER (ORDER BY id), 1000) AS closing_amount
FROM (VALUES
(1, 200, 1000, 1200),
(2, -500, NULL, NULL),
(3, -200, NULL, NULL),
(4, 1000, NULL, NULL)
) AS ledger(id, amount, opening_amount, closing_amount)
)
SELECT
id,
amount,
opening_amount,
closing_amount
FROM ledger_data;


Issue: The LAG() function seems to be giving incorrect results for subsequent rows. I need to calculate the opening_amount and closing_amount in a cumulative way, where each row depends on the previous row's closing balance.

Can anyone suggest how I can fix this query to correctly generate the ledger format?

Continue reading...