SELECT
DATEPART(YEAR, PostingDate) AS year,
DATEPART(MONTH, PostingDate) AS month,
SUM(CASE WHEN G_L_EntryNo = 1 THEN Amount ELSE 0 END) +
SUM(CASE WHEN G_L_EntryNo > 1 AND PostingDate < DATEFROMPARTS(DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate), 1) THEN Amount ELSE 0 END) AS opening_balance,
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)
ORDER BY
DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate);
Result
year month opening_balance change
---------------------------------------
2021 8 0.000 -15.000
2021 9 0.000 -5250.000
2021 10 0.000 -588.000
2021 11 0.000 -1141.980
2021 12 0.000 -2174.000
2022 1 0.000 -210.000
2022 2 0.000 -340.000
2022 3 0.000 -1560.000
Don't worry about minus change balance it is actual data .
Opening balance is not showing previous month ending balance as opening balance. What am I doing wrong?
This is an example answer, it aims to shows how having "sample data" and "expected result" enables someone to trial their answer to your question(s). Note I really do NOT know what the "expected result" should be, so the result you see below may be wrong. If this is the case edit your question and add the correct "expected result" there (please don't use tiny comments for this). Here is some "sample data":
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');
now an example query:
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;
the result of that query:
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 |
You can trial (and/or amend) the above here: fiddle