Explanation of SQL
This SQL will provide a listing of all Customer Credits based on the GL as of a specific date. It can be run for all divisions (use Division = -1) or a specific division.
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> – Customer Credit Report DECLARE @TheDate DateTime; DECLARE @Division Int;
SET @TheDate = '12/31/2008 23:59:59'; SET @Division = 10; – use -1 for all
SELECT
Division.DivisionName AS [Division Name], Account.CompanyName AS [Company Name], Amount AS [Deposit], LastDeposit AS [Last Credit Date], Deposits.AccountID, Deposits.DivisionID
FROM (
SELECT Coalesce(GL.DivisionID, 10) AS DivisionID, AccountID, -SUM(Amount) AS Amount, MAX(EntryDateTime) AS LastDeposit FROM GL WHERE GLAccountID = 23 -- Customer Deposits AND EntryDateTime
You could leave a comment if you were logged in.