control_sql_-_historical_customer_credit_report [Support]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

======  ======



===== 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