(Optimum) Filter Non-Tally Debtor Aging versus Debtor Ledger

Created by Cheng Kah Poh, Modified on Fri, 25 Jun, 2021 at 3:40 PM by QNE Software Sdn bhd

Connect to Database use Microsoft SQL Server Management Studio, Please refer How to Connect to Database use Microsoft SQL Server Management Studio and How to install Microsoft SQL Management Studio
Download .sql script from below link:

https://drive.google.com/open?id=1Dku2XHkBKl8bo5eauhzopqwEmFCa-8y8

Double click on downloaded .sql script file
Change the closing date if required

Click Execute
Result will show which Debtor Aging vs Ledger not tall

BEGIN
DECLARE @DATE DATE = '2016-12-31' ----- Change the Closing Date if required
DECLARE @LEDGER TABLE(CompanyCode VARCHAR(20), LedgerBalance NUMERIC(28,2))
DECLARE @AGING TABLE(CompanyCode VARCHAR(20), AgingBalance NUMERIC(28,2))
DECLARE @RESULT TABLE(CompanyCode VARCHAR(20), LedgerBalance NUMERIC(28,2), AgingBalance NUMERIC(28,2))

-----Ledger-----
INSERT INTO @LEDGER (CompanyCode, LedgerBalance)
SELECT gla.GLAccountCode, SUM(glt.Debit-glt.Credit) 
FROM dbo.GLTransactions glt
JOIN dbo.GLAccounts gla ON gla.Id = glt.GLAccountId
WHERE gla.SpecialAccountId = (SELECT ID FROM SpecialAccounts WHERE SpecialAccountCode = 'AR') 
AND glt.PostingDate <= @DATE AND ISNULL(glt.IsCancelled,0) = 0 AND ISNULL(glt.IsPostDatedCheque,0) = 0
GROUP BY gla.GLAccountCode

-----AGING-----
INSERT INTO @AGING (CompanyCode, AgingBalance)
SELECT CompanyCode, SUM(OutstandingLocal) 
FROM FN_GetAROutstanding(@DATE,'False','False')
GROUP BY CompanyCode

-----RESULT-----
INSERT INTO @RESULT ( CompanyCode, LedgerBalance, AgingBalance)
SELECT L.CompanyCode,L.LedgerBalance, A.AgingBalance 
FROM @Ledger L LEFT JOIN @AGING A
ON L.CompanyCode = A.CompanyCode
WHERE L.LedgerBalance <> A.AgingBalance
ORDER BY L.CompanyCode

SELECT CompanyCode, LedgerBalance, AgingBalance, LedgerBalance - AgingBalance AS Variance 
FROM @RESULT
WHERE LedgerBalance - AgingBalance <> 0

RETURN 
END