Control SQL - GL Out of Balance By Order

This query shows a breakdown of any out-of-balance in the GL broken down by Order.

Low. No data is modified in the running of this query.

-- GL Out of Balance By Order
DECLARE @StartDate DateTime
SET @StartDate = '1/1/2008'
SELECT	
	(SELECT DivisionName AS Division 
		FROM EmployeeGroup 
		WHERE OOBGL.DivisionID = EmployeeGroup.ID) AS Division,
	TransHeader.OrderNumber,
	TransHeader.BillNumber,
	OOBGL.OOBAmount,
	Account.CompanyName,
	TransHeader.Description,
	TransHeader.AccountID,
	OOBGL.TransHeaderID,
	(SELECT MAX(EntryDateTime) 
		FROM GL GL2 
		WHERE GL2.TransactionID = OOBGL.TransHeaderID) AS LastGLEntryDate
FROM
(
	SELECT	COALESCE(DivisionID, 10) AS DivisionID, 
			COALESCE (TransactionID, 0) AS TransHeaderID, 
			SUM(Amount) AS OOBAmount
	FROM	GL
	WHERE EntryDateTime >= @StartDate
	GROUP BY COALESCE(DivisionID, 10), COALESCE (TransactionID, 0)
	HAVING	(SUM(Amount)  0)
) AS OOBGL
LEFT OUTER JOIN TransHeader ON TransHeader.ID = OOBGL.TransHeaderID 
LEFT OUTER JOIN Account ON TransHeader.AccountID = Account.ID
ORDER BY Division, OrderNumber, BillNumber

• Entered : 6/25/2009
• Version : Control 4.3