Skip to content
Advertisement

SQL Query to group opening balance value

I am relatively new to SQL queries, and Stack Overflow has previously pointed me in the right direction, either by reading other peoples issues or answering my specific question. But this one I can not find anything that would resolve my issue.

I have two data tables which I have joined together to create a list of transactions which belong to a particular account. This part I have done and works (probably not the most efficient coding, but it is to the best of my knowledge). I can post the code if required. The results look like this, the Balance column is calculated in the SQL query and is not in any table: enter image description here

This is great but I want to include an Opening Balance line at the start which would sum all records in this account before a specific date and the balance would include the opening amount. I would like it to look like this: enter image description here

Adjusted Code I am using:

SELECT
 Result.TransID,
 Result.TransDate,
 Result.Reference,
 Result.Description,
 Result.Amount,
 SUM(Result.Amount) OVER(PARTITION BY Result.ledgerRef ORDER BY 
 Result.TransID) AS 'Balance'

FROM    
(
    (SELECT 
        -1 TransID,
        NULL AS 'TransDate',
        NULL AS 'Reference',
        OB.Description,
        SUM(OB.Amount) AS 'Amount',
        SUM(OB.Amount) AS 'Balance',
        OB.LedgerRef

    FROM        
        (
            (SELECT
                -1 AS 'ID',
                MAX(T.dtm_TransDate) AS 'TransDate',
                ' ' AS 'Reference',
                'Opening Balance' AS 'Description',
                    SUM(CASE
                            WHEN LT.txt_LedgerTypeRef = 'REV' then -TD.dbl_TransDataAmount
                            WHEN LT.txt_LedgerTypeRef = 'EXP' then TD.dbl_TransDataAmount
                            WHEN LT.txt_LedgerTypeRef = 'ASS' then TD.dbl_TransDataAmount
                            WHEN LT.txt_LedgerTypeRef = 'LIA' then -TD.dbl_TransDataAmount
                            WHEN LT.txt_LedgerTypeRef = 'EQU' then -TD.dbl_TransDataAmount
                            ELSE TD.dbl_TransDataAmount
                        END) AS 'Amount',
                        LT.txt_LedgerTypeRef AS 'LedgerRef'


            FROM dbo.tbl_TransData AS TD

                JOIN dbo.tbl_Trans AS T ON T.int_Trans_ID = TD.int_TransID
                JOIN dbo.tbl_TransType AS TT ON TT.int_TransType_ID = T.int_TransTypeID
                JOIN dbo.tbl_Account AS A ON A.int_Account_ID = TD.int_AccountID
                JOIN dbo.tbl_Ledger AS L ON L.int_Ledger_ID = A.int_LedgerID
                JOIN dbo.tbl_LedgerType AS LT ON LT.int_LedgerType_ID = L.int_LedgerTypeID  

            WHERE
                T.int_BusinessID = 1
                AND T.bit_Archive <> 1
                AND T.bit_Disabled <> 1
                AND T.bit_TransDelete <> 1
                AND T.dtm_TransDate < '2019-01-01'
                AND TD.int_AccountID = 2167

            GROUP BY

                LT.txt_LedgerTypeRef,
                TD.dbl_TransDataAmount
            )

            UNION

            (SELECT
                -1 AS 'ID',
                MAX(T.dtm_TransDate) AS 'TransDate',
                ' ' AS 'Reference',
                'Opening Balance' AS 'Description',
                SUM(CASE
                        WHEN LT.txt_LedgerTypeRef = 'REV' THEN -T.dbl_TransAmount
                        WHEN LT.txt_LedgerTypeRef = 'EXP' THEN T.dbl_TransAmount
                        WHEN LT.txt_LedgerTypeRef = 'ASS' THEN T.dbl_TransAmount
                        WHEN LT.txt_LedgerTypeRef = 'LIA' THEN -T.dbl_TransAmount
                        WHEN LT.txt_LedgerTypeRef = 'EQU' THEN -T.dbl_TransAmount
                        ELSE T.dbl_TransAmount
                    END) AS 'Amount',
                    LT.txt_LedgerTypeRef AS 'LedgerRef'


            FROM dbo.tbl_Trans AS T

            JOIN dbo.tbl_TransType AS TT ON TT.int_TransType_ID = T.int_TransTypeID
            JOIN dbo.tbl_Account AS A ON A.int_Account_ID = T.int_AccountIDBank
            JOIN dbo.tbl_Ledger AS L ON L.int_Ledger_ID = A.int_LedgerID
            JOIN dbo.tbl_LedgerType AS LT ON LT.int_LedgerType_ID = L.int_LedgerTypeID

            WHERE
                T.int_BusinessID = 1
                AND T.bit_Archive <> 1
                AND T.bit_Disabled <> 1
                AND T.bit_TransDelete <> 1
                AND T.dtm_TransDate < '2019-01-01'
                AND T.int_AccountIDBank = 2167

            GROUP BY
                LT.txt_LedgerTypeRef,
                T.dbl_TransAmount
            )
        ) AS OB

    GROUP BY
        OB.Description,
        OB.LedgerRef
    )

    UNION

    (SELECT 
        TR.TransID,
        TR.TransDate,
        TR.Reference,
        TR.Description,
        TR.Amount,
        SUM(TR.amount) OVER(PARTITION BY TR.ledgerRef ORDER BY TR.TransID) AS 'Balance',
        TR.LedgerRef

    FROM    
        (
            (SELECT
                T.int_Trans_ID AS 'TransID',
                T.dtm_TransDate AS 'TransDate',
                concat(TT.txt_TransTypeCode, T.dbl_TransRef) AS 'Reference',
                TD.txt_TransDataDescription AS 'Description',
                    CASE
                        WHEN LT.txt_LedgerTypeRef = 'REV' then -TD.dbl_TransDataAmount
                        WHEN LT.txt_LedgerTypeRef = 'EXP' then TD.dbl_TransDataAmount
                        WHEN LT.txt_LedgerTypeRef = 'ASS' then TD.dbl_TransDataAmount
                        WHEN LT.txt_LedgerTypeRef = 'LIA' then -TD.dbl_TransDataAmount
                        WHEN LT.txt_LedgerTypeRef = 'EQU' then -TD.dbl_TransDataAmount
                        ELSE TD.dbl_TransDataAmount
                    END AS 'Amount',
                    LT.txt_LedgerTypeRef AS 'LedgerRef'

            FROM dbo.tbl_TransData AS TD

                JOIN dbo.tbl_Trans AS T ON T.int_Trans_ID = TD.int_TransID
                JOIN dbo.tbl_TransType AS TT ON TT.int_TransType_ID = T.int_TransTypeID
                JOIN dbo.tbl_Account AS A ON A.int_Account_ID = TD.int_AccountID
                JOIN dbo.tbl_Ledger AS L ON L.int_Ledger_ID = A.int_LedgerID
                JOIN dbo.tbl_LedgerType AS LT ON LT.int_LedgerType_ID = L.int_LedgerTypeID  

            WHERE
                T.int_BusinessID = 1
                AND T.bit_Archive <> 1
                AND T.bit_Disabled <> 1
                AND T.bit_TransDelete <> 1
                AND T.dtm_TransDate >= '2019-01-01'
                AND TD.int_AccountID = 2167
            )
            UNION
            (SELECT
                T.int_Trans_ID AS 'TransID',
                T.dtm_TransDate AS 'TransDate',
                concat(TT.txt_TransTypeCode, T.dbl_TransRef) AS 'Reference',
                T.txt_TransDescription AS 'Description',
                CASE
                    WHEN LT.txt_LedgerTypeRef = 'REV' THEN -T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'EXP' THEN T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'ASS' THEN T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'LIA' THEN -T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'EQU' THEN -T.dbl_TransAmount
                    ELSE T.dbl_TransAmount
                END AS 'Amount',
                LT.txt_LedgerTypeRef AS 'LedgerRef'

            FROM dbo.tbl_Trans AS T

            JOIN dbo.tbl_TransType AS TT ON TT.int_TransType_ID = T.int_TransTypeID
            JOIN dbo.tbl_Account AS A ON A.int_Account_ID = T.int_AccountIDBank
            JOIN dbo.tbl_Ledger AS L ON L.int_Ledger_ID = A.int_LedgerID
            JOIN dbo.tbl_LedgerType AS LT ON LT.int_LedgerType_ID = L.int_LedgerTypeID

            WHERE
                T.int_BusinessID = 1
                AND T.bit_Archive <> 1
                AND T.bit_Disabled <> 1
                AND T.bit_TransDelete <> 1
                AND T.dtm_TransDate >= '2019-01-01'
                AND T.int_AccountIDBank = 2167
            )

        ) AS TR
    )
) AS Result

This all seems to work fine, where I had to union the 2 tables for the opening balance and the transactions, so lots of unions there.

This is what the results look like:

enter image description here

Just one point, on the opening balance line, I would like the Amount to be blank, but if I leave it out of the opening balance query, the balance carried forward is wrong. It is on line 17 of the code. Not sure it can be done.

Once again any advise would be much appreciated.

Advertisement

Answer

Without having all your tables and going through a full setup and sample populating, I can offer the following…

Your INNER query is pre-grabbing all the transactions and details and union together into one set… of which you then outer query sum them.

I would add one more adjust the query something like (and again, just guidance vs full data prep and all).

select
      … original fields and sum(…)( over/order by)
   from
      ( InnerQuery1
        UNION
        InnerQuery2 )

and change to

select
      … original fields and sum(…)( over/order by)
   from
      ( InnerQuery1
           where TransDate > YourCutoffDate
        UNION
        InnerQuery2
           where TransDate > YourCutoffDate
        UNION
        ( select
                -- place-holder field to match the union requirements               
                -1 int_Trans_ID,
                MAX( ExistingDataDate ) AS 'TransDate',
                ' ' as 'Reference',
                'Balance Forward' as 'Description',
                SUM( CASE
                    WHEN LT.txt_LedgerTypeRef = 'REV' THEN -T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'EXP' THEN T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'ASS' THEN T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'LIA' THEN -T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'EQU' THEN -T.dbl_TransAmount
                    ELSE T.dbl_TransAmount
                END ) AS 'Amount',
                LT.txt_LedgerTypeRef AS 'LedgerRef'
            from
                . . .  
            where
                TransDate <= YourCutoffDate                   
            group by
                ?LedgerReg -- unsure per your data sources
        )
     )

In this case, you can always run the new query for balance forward on its own merit to make sure that makes syntax and aggregation sense you INTEND to get. By using the WHERE clause to limit the top queries to only include those AFTER whatever cut-off date, those will be their own. This new query will pre-aggregate into a single row (per ledger) for all transactions PRIOR TO the cutoff leaving only 1 record (per ledger). Using the MAX() on the transaction date will make sure it floats to the top by date order and should be first thus starting your balance forward value.

Hope this make sense as the rest of the query you have is already working. Here, you are in essence just pre-querying all records ON or PRIOR TO the cutoff date and adding to the list. So build this query on its own first, THEN adjust your existing to include as additional union.

For your final removal of opening balance amount. You have a controlled value of the ID = -1. Use a case/when to remove. In your OUTERMOST query, change

Revision to clear opening balance amount line…

Result.Amount,
SUM(Result.Amount) OVER(PARTITION BY Result.ledgerRef ORDER BY

to 

case when int_trans_id < 0 then 0 else Result.Amount end Amount,
SUM(Result.Amount) OVER(PARTITION BY Result.ledgerRef ORDER BY
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement