Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ====== 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 ===== <code 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 </code> ===== Version Information ===== * Entered : 6/25/2009 * Version : Control 4.3