SQL query resulting in opening balance as 0

Question

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?

Answer 1

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

Answer 2

No date can be less than the first day of the same month, but this is what your case expresion calls for:

AND PostingDate < DATEFROMPARTS(DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate), 1) THEN Amount
-- 2023-10-20 cannot be less than 2023-10-01
-- 2023-10-01 cannot be less than 2023-10-01

So you need to alter that case expression e.g. add 1 month to calculated date

AND PostingDate < DATEADD(m,1,DATEFROMPARTS(DATEPART(YEAR, PostingDate), DATEPART(MONTH, PostingDate), 1)) THEN Amount