sql-serverdatepart

SQL query resulting in opening balance as 0


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?


Solution

  • 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