Differences

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

--- control_sql_-_gl_out_of_balance_by_order [2019/01/27 11:28]
127.0.0.1 external edit
+++ control_sql_-_gl_out_of_balance_by_order [2019/02/05 11:19] (current)
admin
@@ Line 23: / Line 23: @@
 <code sql>
 -- GL Out of Balance By Order
-declare @StartDate DateTime
+DECLARE @StartDate DateTime
-set @StartDate = '1/1/2008'
+SET @StartDate = '1/1/2008'
-SELECT
-	(select DivisionName as Division
+SELECT
-		from EmployeeGroup
+    (SELECT DivisionName AS Division
-		where OOBGL.DivisionID = EmployeeGroup.ID) as Division,
+        FROM EmployeeGroup
-	TransHeader.OrderNumber,
+        WHERE OOBGL.DivisionID = EmployeeGroup.ID) AS Division,
-	TransHeader.BillNumber,
+    TransHeader.OrderNumber,
-	OOBGL.OOBAmount,
+    TransHeader.BillNumber,
-	Account.CompanyName,
+    OOBGL.OOBAmount,
-	TransHeader.Description,
+    Account.CompanyName,
-	TransHeader.AccountID,
+    TransHeader.Description,
-	OOBGL.TransHeaderID,
+    TransHeader.AccountID,
-	(select max(EntryDateTime)
+    OOBGL.TransHeaderID,
-		from GL GL2
+    (SELECT MAX(EntryDateTime)
-		where GL2.TransactionID = OOBGL.TransHeaderID) as LastGLEntryDate
+        FROM GL GL2
+        WHERE GL2.TransactionID = OOBGL.TransHeaderID) AS LastGLEntryDate
 FROM
 (
-	SELECT	Coalesce(DivisionID, 10) as DivisionID,
+    SELECT    COALESCE(DivisionID, 10) AS DivisionID,
-			COALESCE (TransactionID, 0) AS TransHeaderID,
+            COALESCE (TransactionID, 0) AS TransHeaderID,
-			SUM(Amount) AS OOBAmount
+            SUM(Amount) AS OOBAmount
-	FROM	GL
+    FROM    GL
-	WHERE EntryDateTime >= @StartDate
+    WHERE EntryDateTime >= @StartDate
-	GROUP BY Coalesce(DivisionID, 10), COALESCE (TransactionID, 0)
+    GROUP BY COALESCE(DivisionID, 10), COALESCE (TransactionID, 0)
-	HAVING	(SUM(Amount)  0)
+    HAVING    (SUM(Amount) <> 0)
 ) AS OOBGL
-LEFT OUTER JOIN TransHeader ON TransHeader.ID = OOBGL.TransHeaderID
+LEFT OUTER JOIN TransHeader ON TransHeader.ID = OOBGL.TransHeaderID
 LEFT OUTER JOIN Account ON TransHeader.AccountID = Account.ID
-order by Division, OrderNumber, BillNumber
+ORDER BY Division, OrderNumber, BillNumber
 </code>