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
- Entered : 6/25/2009
- Version : Control 4.3
You could leave a comment if you were logged in.