By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tblG_L_Entry (
G_L_EntryNo INT,
PostingDate DATE,
Amount DECIMAL(10, 2),
G_L_AccountNo VARCHAR(10)
);
INSERT INTO tblG_L_Entry (G_L_EntryNo, PostingDate, Amount, G_L_AccountNo)
VALUES
(2, '2021-08-01', -15.000, '1010000'),
(2, '2021-09-01', -525, '1010000'),
(2, '2021-10-01', -588.000, '1010000'),
(2, '2021-11-01', -1141.980, '1010000'),
(2, '2021-12-01', -2174.000, '1010000'),
(2, '2022-01-01', -21, '1010000'),
(2, '2022-02-01', -34, '1010000'),
(2, '2022-03-01', -156, '1010000');
8 rows affected
WITH MonthlyChanges
AS (
SELECT
DATEPART(YEAR, PostingDate) AS year
, DATEPART(MONTH, PostingDate) AS month
, SUM(CASE
WHEN G_L_EntryNo > 1
AND PostingDate >= DATEFROMPARTS(DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate), 1)
THEN Amount
ELSE 0
END) AS change
FROM tblG_L_Entry
WHERE G_L_AccountNo = '1010000'
GROUP BY
DATEPART(YEAR, PostingDate)
, DATEPART(MONTH, PostingDate)
)
SELECT
year
, month
, change
, SUM(change) OVER (
ORDER BY year
, month ROWS UNBOUNDED PRECEDING
) AS opening_balance
FROM MonthlyChanges
ORDER BY
year
, month;
| year | month | change | opening_balance |
|---|---|---|---|
| 2021 | 8 | -15.00 | -15.00 |
| 2021 | 9 | -525.00 | -540.00 |
| 2021 | 10 | -588.00 | -1128.00 |
| 2021 | 11 | -1141.98 | -2269.98 |
| 2021 | 12 | -2174.00 | -4443.98 |
| 2022 | 1 | -21.00 | -4464.98 |
| 2022 | 2 | -34.00 | -4498.98 |
| 2022 | 3 | -156.00 | -4654.98 |