Table of Contents

Control SQL - Customer Credit Balance Integrity Check

Explanation of SQL

This SQL compares Customer Credits in the GL with Customer Credits in the Customer Database.

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 
        FROM Division 
        WHERE COALESCE(GLDivisionID, OrderDivisionID) = Division.ID) AS Division,
    (SELECT CompanyName 
        FROM Account
        WHERE ID = COALESCE(B.AccountID, A.GLsAccountID)) AS CompanyName,
    GLAmount AS GLCreditBalance,
    B.CreditBalance AS CustomerCreditBalance,
    COALESCE(GLAmount, 0) - COALESCE(B.CreditBalance,0) AS Difference,
    COALESCE(B.AccountID, A.GLsAccountID) AS AccountID,
    (SELECT MAX(EntryDateTime) 
        FROM GL GL2 
        WHERE GL2.AccountID = A.GLsAccountID
          AND GL2.GLAccountID = 23) AS LastGLCreditDate
 
FROM
(
    SELECT  COALESCE(DivisionID, 10) AS GLDivisionID, 
        AccountID AS GLsAccountID, 
        -SUM(Amount) AS GLAmount
    FROM GL
    WHERE GLAccountID = 23
    GROUP BY COALESCE(DivisionID, 10), AccountID
    HAVING SUM(Amount) <> 0
) AS A
FULL JOIN
(
    SELECT ID AS AccountID, CompanyName, CreditBalance, 
         COALESCE(DivisionID, 10) AS OrderDivisionID
    FROM Account
    WHERE CreditBalance > 0
) AS B ON A.GLsAccountID = B.AccountID
 
WHERE COALESCE(A.GLAmount, 0) <> COALESCE(B.CreditBalance, 0)
 
ORDER BY Division, CompanyName, GLsAccountID

Version Information