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