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.

None. This is a selection query and no data is modified in the running of it.

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
  • Entered : 2/2012
  • Version : 4.5+
You could leave a comment if you were logged in.