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+