SQL SELECT Account_Balance, (This is the life to date balance of this account at the end of a specific Accounting_Period)
Account_Code, (This is your GL Account Code) Accounting_Period, (This is the Accounting_Period) Cost_Centre (This is the cost centre / department) FROM "DataWhse".dbo."Balance_Sheet_Balances";
In a nutshell, what I need is to calculate the movement amount for a specific accounting period.
I need to have a life to date balance of every single account by cost centre by accounting period as well as
a movement amount by account by cost centre for every single accounting period.
I think I have a solution, but I have no idea how to write the script for it. If this is not the correct solution, please provide me with your idea.
I would like to sum the Account_Balance for every accounting period where Accounting Period = Accounting Period - 1. I would then call this account balance, Account_Balance_Previous_Period. By having this, I can now deduct the newly created Account_Balance_Previous_Period from the initial Account_Balance. This should leave me with the movement between the balance of the current month and the balance of the prior month.