Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_historical_customer_credit_report [2019/01/27 11:28]
 control_sql_-_historical_customer_credit_report [2019/01/27 11:28] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +===== 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