Report from Double Entry Accounting

Question

My reputation is not enough to comment on this post: Relational Data Model for Double-Entry Accounting

For the data model from the linked question, can anyone explain the calculation for the closing balance on the AccountStatement, any sample SQL?

• And a small clarification, correct me if I'm wrong. For the calculation of the closing balance for every first day of the month, I have a button that triggers on my module to calculate it?

Answer 1

can any body explain to me how the general ledger transaction table works

In the hope that this is what you are seeking, that this will suffice:

• merely INSERT LedgerTransaction ... for each real world Ledger Transaction

If you are seeking more than that, it means you need accounting basics, which cannot be answered here. Check for the free Tutorials available on the web.

also for the calculation of the closing balance on the AccountStatement any sample sql?

SQL • View

I have upgraded the View from the linked question, to obtain TotalCredit & TotalDebit columns, for all Transactions since the 1st day of the previous month.

CREATE VIEW Account_Current_V 
AS
    SELECT 
        AccountNo,
        Date = DATEADD( DD, -1, GETDATE() ), -- show previous day
        ClosingBalance,
        TotalCredit = (
            -- TotalCredit_Subquery
            SELECT SUM( Amount )
                FROM AccountTransaction
                WHERE AccountNo = @AccountNo
                    AND XactTypeCode_Ext IN ( "AC", "Dp" )
                        -- this month
                    AND DateTime >= CONVERT( CHAR(6), GETDATE(), 2 ) + "01"
                ),
        TotalDebit = (
            -- TotalDebit_Subquery
            SELECT SUM( Amount )
                FROM AccountTransaction
                WHERE AccountNo = @AccountNo
                    AND XactTypeCode_Ext NOT IN ( "AC", "Dp" )
                    AND DateTime >= CONVERT( CHAR(6), GETDATE(), 2 ) + "01"
                ),
        CurrentBalance = ClosingBalance + 
            <TotalCredit_Subquery> - 
            <TotalDebit_Subquery>
        FROM AccountStatement                -- 1st day of this month
        WHERE Date = CONVERT( CHAR(6), GETDATE(), 2 ) + "01"

SQL • MonthEnd • Insert AccountStatement

On the 1st day of the new month, the idea is to close the previous month, dated the 1st of the new month. We use the above View which to obtains the TotalCredit & TotalDebit columns for all Transactions since the 1st day of the previous month.

This is just one Task in the Month End job, on the 1st day of the month. It would normally be run on the batch queue, for all Accounts,with proper transaction control limits (eg. SET ROWCOUNT 500), etc.

INSERT AccountStatement 
    SELECT  AccountNo,
            -- Date: 1st day of this month
            CONVERT( CHAR(6), GETDATE(), 2 ) + "01",
            ACV.CurrentBalance,
            ACV.TotalCredit,
            ACV.TotalDebit
        FROM Account_Current_V    ACV
            JOIN AccountStatement ASS
                ON ACV.AccountNo = ASS.AccountNo
        -- AccountStatements that are not yet MonthEnd-ed
        -- get single row that is newest
        WHERE ASS.Date = (
            SELECT MAX( Date )
                FROM AccountStatement
                WHERE AccountNo = ASS.AccountNo
                )
                -- that is not 1st day of this month
            AND ASS.Date != CONVERT( CHAR(6), GETDATE(), 2 ) + "01"

It is the same for updating the LedgerStatement.

correct me if im wrong, for the calculation of the closing balance for every first day of the next month, i have a button that trigger on my module to. calculate it?

No. Whereas the GUI interface is online, an app of any reasonable complexity needs to run jobs on the back end server. Eg. Month End; Daily backups; Transaction Log roll-ups; etc. Generally there is a set up on the server to do that, else you have to write one.

There will be many Tasks that comprise Month End. This is just one of those Tasks. There is a limit to what you can do in PHP, and I wouldn't dream of doing this in PHP. For technical and modularity reasons, I would place the code for that Task, and all the other Month End Tasks, in a stored proc Account_MonthEnd_btr.

You can't do it from a button because:

• it will hang up the GUI until the Month End Task is finished, which may be more than a few minutes (depends on the number of Accounts, LedgerAccounts, etc).

• it will blow the Transaction Log (if the number of Ledgers or Accounts is at all large). That control as well, has to be in the back end.