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