Differences

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

Link to this comparison view

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> <code sql>
 -- GL Out of Balance By Order -- 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 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 ) 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, OrderNumber,​ BillNumber+ORDER BY Division, OrderNumber,​ BillNumber
 </​code>​ </​code>​