Introduction
This function returns all the accounts and their opening and closing balances, including rows for Net Profit for the year, and Accumulated profit
--call by:
SELECT * FROM [DBNAME].[dbo]._ASf_GLBalances('2018-03-31','ACCU RESULT ACC','CY PROFIT') t
--how to run:
SELECT x.*
,CASE WHEN COALESCE(AT.bIsBalanceSheet,1) = 1 THEN 'Balance sheet' ELSE 'Income statement' END as bIsBalanceSheet
,AT.iReportingGroup
,AT.iReportingGroupSort
FROM _ASf_GLBalances_V3('2021-07-31','ACCU RESULT ACC','CY PROFIT',1) x
left join Accounts A ON x.AccountLink = A.AccountLink
left join _etblGLAccountTypes AT ON A.iAccountType = AT.idGLAccountType
SQL - Function - _ASf_GLBalances_v3 (note: MAJOR speed increase compared to prior version)
--declare @CYClosingDate as DateTime = '2022-01-31'
GO
-- =============================================
-- Author: Asamco BV - Alexander Toufexis
-- Create date: 05/05/16
-- Description: This function resolves GL balances per GL account, as well CY & PY Profit and Accumulated Profit - Evo v7+
-- =============================================
CREATE OR ALTER FUNCTION _ASf_GLBalances_v3 (
-- Add the parameters for the stored procedure here
@CYClosingDate as DateTime, @AccuResultAccount as varchar(250), @CYResultAccount as varchar(250), @IncludeCurrentYearResult bit = 0)
RETURNS @Output TABLE (
AccountLink bigint,
MasterSubAccount varchar(250),
AccountDesc varchar(250),
AccountType varchar(250),
PriorYearBalance float,
OpeningBalance float,
ClosingBalance float
)
BEGIN
--Usage:
--SELECT * FROM _ASf_GLBalances_v3('2021-06-30','ACCU RESULT ACC','CY PROFIT',1)
--Handling defaults:
SELECT @AccuResultAccount = COALESCE(@AccuResultAccount,'AccuResult')
SELECT @CYResultAccount = COALESCE(@CYResultAccount,'CYResult')
------------------PERIOD CALCS:------------------------
--Handling date calculations:
DECLARE @CY datetime
--SELECT @PY = @PYClosingDate-- '2013-12-31'
SELECT @CY = @CYClosingDate --'2014-12-31'
DECLARE @PY datetime --Resolving PY date, in case it's not passed as a variable.
SELECT @PY = (SELECT MAX(dPeriodDate) FROM _etblPeriod WHERE iYearID < (SELECT t3.iYearID FROM _etblPeriod t3 WHERE t3.idPeriod = (SELECT MAX(t2.idPeriod)+1 FROM _etblPeriod t2 WHERE dPeriodDate < @CY)))
DECLARE @PeriodFrom int, @PeriodEnd int
SELECT @PeriodFrom = COALESCE((Select Max(idPeriod) + 1 from _etblPeriod where dPeriodDate < @PY),0)
SELECT @PeriodEnd = (Select Max(idPeriod) + 1 from _etblPeriod where dPeriodDate < @CY)
DECLARE @PriorYear int,@CurrentYear int, @PY_StartPeriod int
SELECT @PriorYear = (Select iYearID from _etblPeriod where idPeriod = @PeriodFrom)
SELECT @PY_StartPeriod = (Select MIN(idPeriod) from _etblPeriod where iYearID = @PriorYear)
DECLARE @CY_Result float, @PY_Result float, @CY_AccumulatedProfit float, @PY_AccumulatedProfit float
--CY Result, PY Result, CY Accumulated Profit, PY Accumulated
SELECT @CY_Result =
(SELECT ROUND(SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)),2)
FROM PostGL t2
LEFT JOIN Accounts t3 ON t2.AccountLink = t3.AccountLink
LEFT JOIN _etblGLAccountTypes AT ON t3.iAccountType = AT.idGLAccountType
WHERE AT.bIsBalanceSheet = 0
AND TxDate > @PY AND TxDate <= @CY
),
@CY_AccumulatedProfit =
(ROUND(COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit)
FROM _etblAccBlnc BLC
LEFT JOIN Accounts ACC ON ACC.AccountLink = BLC.iAccBlncAccountID
LEFT JOIN _etblGLAccountTypes AT ON ACC.iAccountType = AT.idGLAccountType
WHERE AT.bIsBalanceSheet = 0 AND BLC.iAccBlncPeriodID < @PeriodFrom),0)
+ COALESCE((SELECT ROUND(SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)),2)
FROM PostGL P2
LEFT JOIN Accounts t3 ON P2.AccountLink = t3.AccountLink
LEFT JOIN _etblGLAccountTypes AT ON t3.iAccountType = AT.idGLAccountType
WHERE AT.bIsBalanceSheet = 0
AND P2.Period >= @PeriodFrom AND P2.TxDate <= @PY),0)
,2)
),
@PY_Result =
(COALESCE((SELECT ROUND(SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)),2)
FROM PostGL P2
LEFT JOIN Accounts t3 ON P2.AccountLink = t3.AccountLink
LEFT JOIN _etblGLAccountTypes AT ON t3.iAccountType = AT.idGLAccountType
WHERE AT.bIsBalanceSheet = 0
AND P2.Period >= @PY_StartPeriod AND P2.TxDate <= @PY),0)
),
@PY_AccumulatedProfit =
ROUND(COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit)
FROM _etblAccBlnc BLC
LEFT JOIN Accounts ACC ON ACC.AccountLink = BLC.iAccBlncAccountID
LEFT JOIN _etblGLAccountTypes AT ON ACC.iAccountType = AT.idGLAccountType
WHERE AT.bIsBalanceSheet = 0 AND BLC.iAccBlncPeriodID < @PY_StartPeriod),0),2)
--SELECT @CY_Result, @CY_AccumulatedProfit , @PY_Result,@PY_AccumulatedProfit
------------INSERTING CY RESULT AND ACCUMULATED RESULT----------------
--CY: UNCOMMENT LINE BELOW, AND YOU;ll GET CURRENT YEAR RESULT AS WELL - COULD BE USED WHEN ONLY DOING BS ACCOUNT CONVERSION
IF @IncludeCurrentYearResult = 1
BEGIN
INSERT INTO @Output (AccountLink,MasterSubAccount, AccountDesc, AccountType,PriorYearBalance, OpeningBalance, ClosingBalance) VALUES (-2,@CYResultAccount, 'CY Net Profit','Retained Earnings', @PY_Result,0, @CY_Result)
END
--Accumulated:
INSERT INTO @Output (AccountLink,MasterSubAccount, AccountDesc, AccountType,PriorYearBalance, OpeningBalance, ClosingBalance) VALUES (-1,@AccuResultAccount, 'Accumulated surplus','Retained Earnings', @PY_AccumulatedProfit, @PY_AccumulatedProfit+@PY_Result, @CY_AccumulatedProfit)
--ACTUAL BALANCES QRY:-------------------------------------------------------------------
INSERT INTO @Output (AccountLink,MasterSubAccount, AccountDesc, AccountType,PriorYearBalance, OpeningBalance, ClosingBalance)
SELECT Balances.AccountLink,A.Master_Sub_Account,A.Description,AT.cAccountTypeDescription
, SUM(PYBalance) as PYBalance
, CASE WHEN AT.bIsBalanceSheet = 1 THEN SUM(PYBalance) ELSE 0 END as OpeningBalance
, SUM(CYBalance) as CYBalance
FROM
(/*start of all balances*/
/*AccPrev - old old balances*/
SELECT iAccPrevAccountID as AccountLink,
SUM(CASE WHEN AT.bIsBalanceSheet = 1 THEN ROUND(Cast(fBFDebit as Decimal(22,5)),2)-ROUND(Cast(fBFCredit as Decimal(22,5)),2) ELSE 0 END ) PYBalance
,SUM(CASE WHEN AT.bIsBalanceSheet = 1 THEN ROUND(Cast(fBFDebit as Decimal(22,5)),2)-ROUND(Cast(fBFCredit as Decimal(22,5)),2) ELSE 0 END ) CYBalance
FROM [_etblAccPrev] P
LEFT JOIN Accounts A ON P.iAccPrevAccountID = A.AccountLink
LEFT JOIN _etblGLAccountTypes AT ON A.iAccountType = AT.idGLAccountType
GROUP BY iAccPrevAccountID
UNION ALL
/*AccBlnc balances up to (not including) last period:*/
SELECT BLC.iAccBlncAccountID
, SUM(CASE WHEN BLC.iAccBlncPeriodID <= @PeriodFrom AND (PD.iYearID = @PriorYear OR AT.bIsBalanceSheet = 1) THEN ROUND(Cast(BLC.fActualDebit as Decimal(22,5)),2) - ROUND(Cast(BLC.fActualCredit as Decimal(22,5)),2) ELSE 0 END) as PYBalance
, SUM(CASE WHEN BLC.iAccBlncPeriodID < @PeriodEnd AND (BLC.iAccBlncPeriodID > @PeriodFrom OR AT.bIsBalanceSheet = 1) THEN ROUND(Cast(BLC.fActualDebit as Decimal(22,5)),2) - ROUND(Cast(BLC.fActualCredit as Decimal(22,5)),2) ELSE 0 END) as CYBalance
FROM _etblAccBlnc BLC with(NOLOCK)
LEFT JOIN _etblPeriod PD with(NOLOCK) ON BLC.iAccBlncPeriodID = PD.idPeriod
LEFT JOIN Accounts A ON BLC.iAccBlncAccountID = A.AccountLink
LEFT JOIN _etblGLAccountTypes AT ON A.iAccountType = AT.idGLAccountType
WHERE BLC.iAccBlncPeriodID > 0
GROUP BY BLC.iAccBlncAccountID
UNION ALL
/*PostGL balances for the last period up to the date. (needed to handle date based reporting)*/
SELECT P2.AccountLink
, 0 as PYBalance
, SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)) as CYBalance
FROM PostGL P2 WHERE P2.Period >= @PeriodEnd AND P2.TxDate <= @CY
GROUP BY P2.AccountLink
) Balances
LEFT JOIN Accounts A ON Balances.AccountLink = A.AccountLink
LEFT JOIN _etblGLAccountTypes AT ON A.iAccountType = AT.idGLAccountType
WHERE
(A.[AccountLevel] IN(0,2))
GROUP BY Balances.AccountLink,A.Master_Sub_Account,A.Description,AT.cAccountTypeDescription, AT.bIsBalanceSheet
HAVING SUM(Balances.PYBalance) <> 0 OR SUM(Balances.CYBalance) <> 0 /*only accs with a value*/
--CREATE NONCLUSTERED INDEX [ix__etblGLAccountTypes__bIsBalanceSheet] ON [dbo].[_etblGLAccountTypes] ( [bIsBalanceSheet] );
RETURN --table.
END
SQL - Function - OLD!
SQL Function: _ASf_GLBalances
-- =============================================
-- Author: Asamco BV - Alexander Toufexis
-- Create date: 05/05/16
-- Description: This function resolves GL balances per GL account, as well CY & PY Profit and Accumulated Profit - Evo v7+
-- =============================================
CREATE FUNCTION _ASf_GLBalances (
-- Add the parameters for the stored procedure here
@CYClosingDate as DateTime, @AccuResultAccount as varchar(250), @CYResultAccount as varchar(250))
RETURNS @Output TABLE (
MasterSubAccount varchar(250),
AccountDesc varchar(250),
AccountType varchar(250),
OpeningBalance float,
ClosingBalance float
)
BEGIN
--Usage:
--SELECT * FROM _ASf_GLBalances('2017-06-30','ACCU RESULT ACC','CY PROFIT')
--Start of Alex awesome query:
--Handling defaults:
SELECT @AccuResultAccount = COALESCE(@AccuResultAccount,'AccuResult')
SELECT @CYResultAccount = COALESCE(@CYResultAccount,'CYResult')
--Handling date calculations:
DECLARE @CY datetime
--SELECT @PY = @PYClosingDate-- '2013-12-31'
SELECT @CY = @CYClosingDate --'2014-12-31'
DECLARE @PY datetime --Resolving PY date, in case it's not passed as a variable.
SELECT @PY = (SELECT MAX(dPeriodDate) FROM _etblPeriod WHERE iYearID < (SELECT t3.iYearID FROM _etblPeriod t3 WHERE t3.idPeriod = (SELECT MAX(t2.idPeriod)+1 FROM _etblPeriod t2 WHERE dPeriodDate < @CY)))
DECLARE @PeriodFrom int
DECLARE @PeriodEnd int
SELECT @PeriodFrom = COALESCE((Select Max(idPeriod) + 1 from _etblPeriod where dPeriodDate < @PY),0)
SELECT @PeriodEnd = (Select Max(idPeriod) + 1 from _etblPeriod where dPeriodDate < @CY)
DECLARE @PriorYear int
DECLARE @CurrentYear int
DECLARE @PY_StartPeriod int
SELECT @PriorYear = (Select iYearID from _etblPeriod where idPeriod = @PeriodFrom)
SELECT @PY_StartPeriod = (Select MIN(idPeriod) from _etblPeriod where iYearID = @PriorYear)
--SELECT @CurrentYear = (Select iYearID from _etblPeriod where idPeriod = @PeriodEnd)
DECLARE @CY_Result float
DECLARE @PY_Result float
DECLARE @CY_AccumulatedProfit float
DECLARE @PY_AccumulatedProfit float
--CY Result, PY Result, CY Accumulated Profit, PY Accumulated
SELECT @CY_Result =
(SELECT ROUND(SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)),2)
FROM PostGL t2
LEFT JOIN Accounts t3 ON t2.AccountLink = t3.AccountLink
LEFT JOIN _etblGLAccountTypes AT ON t3.iAccountType = AT.idGLAccountType
WHERE AT.bIsBalanceSheet = 0
AND TxDate > @PY AND TxDate <= @CY
),
@CY_AccumulatedProfit =
(ROUND(COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit)
FROM _etblAccBlnc BLC
LEFT JOIN Accounts ACC ON ACC.AccountLink = BLC.iAccBlncAccountID
LEFT JOIN _etblGLAccountTypes AT ON ACC.iAccountType = AT.idGLAccountType
WHERE AT.bIsBalanceSheet = 0 AND BLC.iAccBlncPeriodID < @PeriodFrom),0)
+ COALESCE((SELECT ROUND(SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)),2)
FROM PostGL P2
LEFT JOIN Accounts t3 ON P2.AccountLink = t3.AccountLink
LEFT JOIN _etblGLAccountTypes AT ON t3.iAccountType = AT.idGLAccountType
WHERE AT.bIsBalanceSheet = 0
AND P2.Period >= @PeriodFrom AND P2.TxDate <= @PY),0)
,2)
),
@PY_Result =
(COALESCE((SELECT ROUND(SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)),2)
FROM PostGL P2
LEFT JOIN Accounts t3 ON P2.AccountLink = t3.AccountLink
LEFT JOIN _etblGLAccountTypes AT ON t3.iAccountType = AT.idGLAccountType
WHERE AT.bIsBalanceSheet = 0
AND P2.Period >= @PY_StartPeriod AND P2.TxDate <= @PY),0)
),
@PY_AccumulatedProfit =
ROUND(COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit)
FROM _etblAccBlnc BLC
LEFT JOIN Accounts ACC ON ACC.AccountLink = BLC.iAccBlncAccountID
LEFT JOIN _etblGLAccountTypes AT ON ACC.iAccountType = AT.idGLAccountType
WHERE AT.bIsBalanceSheet = 0 AND BLC.iAccBlncPeriodID < @PY_StartPeriod),0),2)
--SELECT @CY_Result, @CY_AccumulatedProfit , @PY_Result,@PY_AccumulatedProfit
------------INSERTING CY RESULT AND ACCUMULATED RESULT----------------
--CY: UNCOMMENT LINE BELOW, AND YOU;ll GET CURRENT YEAR RESULT AS WELL - COULD BE USED WHEN ONLY DOING BS ACCOUNT CONVERSION
--INSERT INTO @Output (MasterSubAccount, AccountDesc, AccountType, OpeningBalance, ClosingBalance) VALUES (@CYResultAccount, 'CY Net Profit','Retained Earnings', @PY_Result, @CY_Result)
--Accumulated:
INSERT INTO @Output (MasterSubAccount, AccountDesc, AccountType, OpeningBalance, ClosingBalance) VALUES (@AccuResultAccount, 'Accumulated surplus','Retained Earnings', @PY_AccumulatedProfit, @CY_AccumulatedProfit)
--RETURN
--ACTUAL QRY:
INSERT INTO @Output (MasterSubAccount, AccountDesc, AccountType, OpeningBalance, ClosingBalance)
(SELECT FunctionItem as MasterSubAccount, AccountDesc, Account_Type as AccountType
, t.PY_Closing as OpeningBalance, t.CY_Closing as ClosingBalance
--, SUM(t.PY_Closing) as OpeningBalance, SUM(t.CY_Closing) as ClosingBalance
FROM
(SELECT
FunctionItem = Accounts.Master_Sub_Account,
-------CHANGE THIS PART IN CASE OF DIFFERENT SEGMENT POSITIONS OF REQUIRED COMBINATION!!!!-----------------
--COALESCE((SELECT cCode FROM _etblGLSegment WHERE idSegment = Accounts.iGLSegment3ID AND iSegmentNo = 3),'')
--+ COALESCE((SELECT cCode FROM _etblGLSegment WHERE idSegment = Accounts.iGLSegment0ID AND iSegmentNo = 0),''),
-------END OF PART TO CHANGE-----------------
--Accounts.[Master_Sub_Account],
Accounts.[Description] as AccountDesc,
AT.cAccountTypeDescription as Account_Type,
PY_Closing =
CASE
WHEN bIsBalanceSheet = 1 THEN
ROUND(COALESCE((SELECT SUM(ROUND(Cast(fBFDebit as Decimal(22,5)),2)-ROUND(Cast(fBFCredit as Decimal(22,5)),2)) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
+ COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit) FROM _etblAccBlnc BLC WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID < @PeriodFrom AND BLC.iAccBlncPeriodID > 0),0)
+ COALESCE((SELECT SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period >= @PeriodFrom AND P2.TxDate <= @PY),0),2)
ELSE
ROUND(COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit) FROM _etblAccBlnc BLC LEFT JOIN _etblPeriod PD ON BLC.iAccBlncPeriodID = PD.idPeriod WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID < @PeriodFrom AND PD.iYearID = @PriorYear),0)
+ COALESCE((SELECT SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period >= @PeriodFrom AND P2.TxDate <= @PY),0),2)
END
,CY_Closing =
CASE
WHEN bIsBalanceSheet = 1 THEN
ROUND(COALESCE((SELECT SUM(ROUND(Cast(fBFDebit as Decimal(22,5)),2)-ROUND(Cast(fBFCredit as Decimal(22,5)),2)) FROM [_etblAccPrev] WHERE iAccPrevAccountID = Accounts.AccountLink),0)
+ COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit) FROM _etblAccBlnc BLC LEFT JOIN _etblPeriod PD ON BLC.iAccBlncPeriodID = PD.idPeriod WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID < @PeriodEnd AND BLC.iAccBlncPeriodID > 0),0)
+ COALESCE((SELECT SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period >= @PeriodEnd AND P2.TxDate <= @CY),0),2)
ELSE
ROUND(COALESCE((SELECT SUM(BLC.fActualDebit - BLC.fActualCredit) FROM _etblAccBlnc BLC LEFT JOIN _etblPeriod PD ON BLC.iAccBlncPeriodID = PD.idPeriod WHERE BLC.iAccBlncAccountID = Accounts.AccountLink AND BLC.iAccBlncPeriodID < @PeriodEnd AND BLC.iAccBlncPeriodID > @PeriodFrom),0)
+ COALESCE((SELECT SUM(ROUND(Cast(DEBIT as Decimal(22,5)),2)-ROUND(Cast(CREDIT as Decimal(22,5)),2)) FROM PostGL P2 WHERE Accounts.AccountLink = P2.AccountLink AND P2.Period >= @PeriodEnd AND P2.TxDate <= @CY),0),2)
END
FROM Accounts
LEFT JOIN _etblGLAccountTypes AT ON Accounts.iAccountType = AT.idGLAccountType
WHERE
(Accounts.[AccountLevel] <> 1)) t WHERE t.PY_Closing <> 0 OR t.CY_Closing <> 0
--Optional; only accounts that have values:
--GROUP BY FunctionItem, AccountDesc, Account_Type
--HAVING SUM(t.PY_Closing) <> 0 OR SUM(t.CY_Closing) <> 0
) --END Of insert into @Output
--RETURN @Output
RETURN
END