Differences

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

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