Differences

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

Link to this comparison view

control_sql_-_historical_deposits_report [2019/01/27 11:28]
control_sql_-_historical_deposits_report [2019/01/27 11:28] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +This SQL will provide a listing of all Customer Deposits 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 Deposit Report
 +DECLARE @TheDate DateTime;
 +DECLARE @Division Int;
 +SET @TheDate = ;   -- '12/31/2008 23:59:59'
 +SET @Division = ; -- use -1 for all
 + 
 +SELECT 
 +  Division.DivisionName AS [Division Name],
 +  TH.OrderNumber AS [ORDER Number],
 +  TH.OrderCreatedDate AS [Order Date],
 +  substring(TH.Description, 1, 50) AS [Order Description],
 +  Account.CompanyName AS [Company Name],
 +  Amount AS [Deposit],
 +  LastDeposit as [Last Deposit Date],
 +  TH.SaleDate AS [Order Sales Date],
 +  TH.ClosedDate AS [Order Closed Date],
 +  TH.StatusText as [Current Status],
 +  Deposits.TransactionID AS TransHeaderID, 
 +  Deposits.AccountID,
 +  Deposits.DivisionID
 + 
 +FROM 
 +(
 +   SELECT Coalesce(GL.DivisionID, 10) as DivisionID, AccountID, TransactionID, -SUM(Amount) as Amount, MAX(EntryDateTime) as LastDeposit
 +   FROM GL
 +   WHERE GLAccountID = 24  -- Customer Deposits
 +     AND EntryDateTime