Explanation of SQL
This SQL gives a comparison of the Reconciliation entries (in the Journal) with all of the GL entries that were reconciled by it. It also gives grand totals of the two different approaches, which should match.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
DECLARE @GLAccountID INT; DECLARE @StartDate datetime; SET @GLAccountID = 90; SET @StartDate = '7/30/2011'; SELECT * FROM ( SELECT ID AS ReconciliationID, MONTH(StartDateTime) AS StartMonth, DAY(StartDateTime) AS StartDay, YEAR(StartDateTime) AS StartYear, MONTH(EndDateTime) AS EndMonth, DAY(EndDateTime) AS EndDay, YEAR(EndDateTime) AS EndYear, SummaryAmount AS StartingBalance, DetailAmount AS EndingBalance, SummaryAmount - DetailAmount AS ChangeInBalance, UseActualTime AS InProgress, LinkID AS GLAccount, (SELECT AccountName FROM GLAccount WHERE ID = LinkID) AS AccountName, CAST(BillingNotes AS XML) AS OtherCharges FROM Journal WHERE ClassTypeID = 9750 AND EndDateTime > @StartDate AND LinkID = @GLAccountID ) Rs FULL OUTER JOIN ( SELECT ReconciliationID, EntryDateTime, CASE WHEN Amount > 0 THEN Amount ELSE 0 END AS Debit, CASE WHEN Amount < 0 THEN -Amount ELSE 0 END AS Credit, Description, ReconciliationDateTime, (SELECT AccountName FROM GLAccount WHERE ID = GLAccountID) AS AccountName, SUM(CASE WHEN Amount > 0 THEN Amount ELSE 0 END) OVER (partition BY ReconciliationID) AS TotalDebit, SUM(CASE WHEN Amount < 0 THEN -Amount ELSE 0 END) OVER (partition BY ReconciliationID) AS TotalCredit, SUM(Amount) OVER (partition BY ReconciliationID) AS BankChangeChange FROM GL WHERE ReconciliationID IS NOT NULL AND ReconciliationDateTime > @StartDate AND GLAccountID = @GLAccountID ) GLs ON Rs.ReconciliationID = GLs.ReconciliationID ORDER BY Rs.ReconciliationID DESC, SIGN(GLs.Debit), GLs.EntryDateTime
Version Information
- Entered : 2/2012
- Version : 4.5+
You could leave a comment if you were logged in.