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.

None. This is a selection query and no data is modified in the running of it.

<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