Table of Contents

Control SQL - Vendor Credit Balance

Explanation of SQL

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

Risk of Data Corruption if Run Improperly

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

SQL

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

Version Information