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] admin |
||
---|---|---|---|
Line 23: | Line 23: | ||
<code sql> | <code sql> | ||
-- GL Out of Balance By Order | -- GL Out of Balance By Order | ||
- | declare | + | DECLARE |
- | set @StartDate = ' | + | SET @StartDate = ' |
- | SELECT | + | |
- | (select | + | SELECT |
- | from EmployeeGroup | + | (SELECT |
- | where | + | |
- | TransHeader.OrderNumber, | + | |
- | 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 | + | |
+ | | ||
FROM | FROM | ||
( | ( | ||
- | SELECT Coalesce(DivisionID, | + | |
- | COALESCE (TransactionID, | + | COALESCE (TransactionID, |
- | SUM(Amount) AS OOBAmount | + | SUM(Amount) AS OOBAmount |
- | FROM GL | + | FROM GL |
- | WHERE EntryDateTime >= @StartDate | + | WHERE EntryDateTime >= @StartDate |
- | GROUP BY Coalesce(DivisionID, | + | GROUP BY COALESCE(DivisionID, |
- | HAVING (SUM(Amount) | + | HAVING |
) 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, | + | ORDER BY Division, OrderNumber, |
</ | </ | ||