Use newer view. See this page included:
Unable to render {include} The included page could not be found.
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_V3('2021-07-31','ACCU RESULT ACC','CY PROFIT',0) 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',0) 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