Control SQL - Vendor Credit Balance

This query compares the GL record of all Vendor Credit Balances with the record in the Vendor (account table).

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

SELECT  
    (SELECT DivisionName AS Division 
        FROM EmployeeGroup 
        WHERE COALESCE(A.DivisionID, Account.DivisionID) = EmployeeGroup.ID) AS Division,
    Account.CompanyName,
    Account.VendorCreditBalance,
    GLAmount AS GLCreditBalance,
    COALESCE(GLAmount,0) - COALESCE(Account.VendorCreditBalance,0) AS Difference,
    Account.ID AS AccountID,
    (SELECT MAX(EntryDateTime) 
        FROM GL GL2 
        WHERE GL2.AccountID = Account.ID
          AND GL2.GLAccountID = 25) AS LastGLCreditDate
FROM    
(
    SELECT  COALESCE(DivisionID, 10) AS DivisionID, 
            AccountID AS GLsAccountID, 
            SUM(Amount) GLAmount
    FROM GL
    WHERE GLAccountID = 25
    GROUP BY COALESCE(DivisionID, 10), AccountID
    HAVING SUM(Amount) <> 0
) AS A
FULL JOIN
(
    SELECT ID AS VendorAccountID
    FROM Account
    WHERE VendorCreditBalance <> 0
) AS B ON A.GLsAccountID = B.VendorAccountID
 
LEFT JOIN Account ON Account.ID = COALESCE(A.GLsAccountID, B.VendorAccountID)
WHERE COALESCE(A.GLAmount, 0) <> COALESCE(Account.VendorCreditBalance, 0)
ORDER BY Division, CompanyName, GLsAccountID

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