Differences

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

Link to this comparison view

Next revision 		Previous revision
control_sql_-_find_gl_out_of_balances [2019/01/27 11:28]
127.0.0.1 external edit 		control_sql_-_find_gl_out_of_balances [2019/02/05 11:18] (current)
admin
Line 1: Line 1:
 ====== Control SQL - Find GL Out of Balance ====== ====== Control SQL - Find GL Out of Balance ======
- 
- 
  
 ===== Explanation of SQL ===== ===== Explanation of SQL =====
  
 +This query is run to identify any places the GL:
  
- 
-This query is run to identify any places the GL: 
   * Is Out of Balance Overall   * Is Out of Balance Overall
   * Is Out of Balance for a Division   * Is Out of Balance for a Division
   * Will result in an improper Balance Sheet/Income Statement   * Will result in an improper Balance Sheet/Income Statement
-    * This part not implemented yet+      * This part not implemented yet
   * Uses any non-existend GL Accounts   * Uses any non-existend GL Accounts
-    * This part not implemented yet +      * This part not implemented yet
- +
  
 ===== Risk of Data Corruption if Run Improperly ===== ===== Risk of Data Corruption if Run Improperly =====
  
 **None**. This is a selection query and no data is modified in the running of it. **None**. This is a selection query and no data is modified in the running of it.
- 
- 
  
 ===== SQL ===== ===== SQL =====
- 
- 
  
 <code sql> <code sql>
 -- GL Out of Balance Breakdown -- GL Out of Balance Breakdown
    
-declare @StartDate DateTime +  
-declare @EndDate DateTime+DECLARE @StartDate DateTime 
 +DECLARE @EndDate DateTime
 SET @StartDate = '1/1/2008' SET @StartDate = '1/1/2008'
 SET @EndDate   = '1/1/2020' SET @EndDate   = '1/1/2020'
Line 39: Line 31:
         FROM EmployeeGroup         FROM EmployeeGroup
         WHERE OOBGL.DivisionID = EmployeeGroup.ID) AS Division,         WHERE OOBGL.DivisionID = EmployeeGroup.ID) AS Division,
-    cast( +    CAST( 
-        Cast(OOBGL.TheMonth AS varchar(2)) +        CAST(OOBGL.TheMonth AS VARCHAR(2)) 
-         + '/' + Cast(OOBGL.TheDay AS varchar(2)) +         + '/' + CAST(OOBGL.TheDay AS VARCHAR(2)) 
-         + '/' + Cast(OOBGL.TheYear AS varchar(4))+         + '/' + CAST(OOBGL.TheYear AS VARCHAR(4))
         AS DateTime ) AS TheDate,         AS DateTime ) AS TheDate,
     OOBGL.TheHour,     OOBGL.TheHour,
Line 53: Line 45:
     TransHeader.AccountID,     TransHeader.AccountID,
     OOBGL.TransHeaderID,     OOBGL.TransHeaderID,
-    (SELECT max(EntryDateTime)+    (SELECT MAX(EntryDateTime)
         FROM GL GL2         FROM GL GL2
         WHERE GL2.TransactionID = OOBGL.TransHeaderID) AS LastGLEntryDate         WHERE GL2.TransactionID = OOBGL.TransHeaderID) AS LastGLEntryDate
 FROM FROM
 ( (
-    SELECT    Coalesce(DivisionID, 10) AS DivisionID,+    SELECT    COALESCE(DivisionID, 10) AS DivisionID,
             COALESCE (TransactionID, 0) AS TransHeaderID,             COALESCE (TransactionID, 0) AS TransHeaderID,
-            DatePart(Year, EntryDateTime) AS TheYear, +            DatePart(YEAR, EntryDateTime) AS TheYear, 
-            DatePart(Month, EntryDateTime) AS TheMonth, +            DatePart(MONTH, EntryDateTime) AS TheMonth, 
-            DatePart(Day, EntryDateTime) AS TheDay, +            DatePart(DAY, EntryDateTime) AS TheDay, 
-            DatePart(Hour, EntryDateTime) AS TheHour,+            DatePart(HOUR, EntryDateTime) AS TheHour,
             OffBalanceSheet,             OffBalanceSheet,
             SUM(Amount) AS OOBAmount             SUM(Amount) AS OOBAmount
     FROM    Ledger     FROM    Ledger
     WHERE EntryDateTime BETWEEN @StartDate AND @EndDate     WHERE EntryDateTime BETWEEN @StartDate AND @EndDate
-    GROUP BY Coalesce(DivisionID, 10),+    GROUP BY COALESCE(DivisionID, 10),
         COALESCE (TransactionID, 0),         COALESCE (TransactionID, 0),
-        DatePart(Year, EntryDateTime), +        DatePart(YEAR, EntryDateTime), 
-        DatePart(Month, EntryDateTime), +        DatePart(MONTH, EntryDateTime), 
-        DatePart(Day, EntryDateTime), +        DatePart(DAY, EntryDateTime), 
-        DatePart(Hour, EntryDateTime),+        DatePart(HOUR, EntryDateTime),
         OffBalanceSheet         OffBalanceSheet
-    HAVING    (SUM(Amount)  0)+    HAVING    (SUM(Amount) <> 0)
 ) AS OOBGL ) 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 LEFT OUTER JOIN Account ON TransHeader.AccountID = Account.ID
-  
 ORDER BY Division, TheDate DESC, TheHour DESC, OrderNumber, BillNumber ORDER BY Division, TheDate DESC, TheHour DESC, OrderNumber, BillNumber
 +
 </code> </code>
  
 +===== Version Information =====
  
- +  * Entered : __/__ /2009
-===== Version Information ===== +
-  * Entered : __/__/2009+
   * Version :   * Version :
- 
-