- 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
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article