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