Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ====== ====== ===== 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