Differences
This shows you the differences between two versions of the page.
control_sql_-_order_deposit_integrity_check [2019/01/27 11:29] 127.0.0.1 external edit |
control_sql_-_order_deposit_integrity_check [2019/02/05 11:29] (current) admin |
||
---|---|---|---|
Line 22: | Line 22: | ||
<code sql> | <code sql> | ||
- | select | + | SELECT |
- | (select | + | (SELECT |
- | from Division | + | |
- | where | + | |
- | (Select | + | (SELECT |
- | from TransHeader | + | |
- | where | + | |
- | GLCredit, | + | GLCredit, |
- | (Select | + | (SELECT |
- | from TransHeader | + | |
- | where | + | |
- | coalesce(GLCredit, 0) - coalesce(B.PaymentTotal, | + | |
- | (Select | + | (SELECT |
- | from TransHeader | + | |
- | where | + | |
- | coalesce(THTransHeaderID, | + | |
- | (select max(EntryDateTime) | + | (SELECT MAX(EntryDateTime) |
- | from GL GL2 | + | |
- | where | + | |
- | from | + | FROM |
( | ( | ||
- | select coalesce(DivisionID, | + | SELECT COALESCE(DivisionID, |
- | TransactionID | + | TransactionID |
- | -sum(Amount) GLCredit | + | -SUM(Amount) GLCredit |
- | from GL | + | |
- | where GLAccountID = 24 | + | |
- | group by Coalesce(DivisionID, | + | GROUP BY COALESCE(DivisionID, |
- | having sum(Amount) | + | |
- | ) as A | + | ) AS A |
- | full join | + | |
+ | FULL JOIN | ||
( | ( | ||
- | select ID as THTransHeaderID, | + | SELECT |
- | OrderNumber, | + | OrderNumber, |
- | PaymentTotal, | + | PaymentTotal, |
- | StatusText | + | StatusText |
- | from TransHeader | + | |
- | where StatusID | + | |
- | ) as B on A.GLTransHeaderID = B.THTransHeaderID | + | ) AS B ON A.GLTransHeaderID = B.THTransHeaderID |
- | where coalesce(A.GLCredit, | + | |
- | order by Division, OrderNumber | + | WHERE COALESCE(A.GLCredit, |
+ | |||
+ | ORDER BY Division, OrderNumber | ||
</ | </ | ||
Line 71: | Line 74: | ||
<code sql> | <code sql> | ||
- | select | + | SELECT |
- | (select | + | (SELECT |
- | from EmployeeGroup | + | |
- | where | + | |
- | (Select | + | (SELECT |
- | from TransHeader | + | |
- | where | + | |
- | GLCredit, | + | GLCredit, |
- | (Select | + | (SELECT |
- | from TransHeader | + | |
- | where | + | |
- | coalesce(GLCredit, 0) - coalesce(B.DepositAmount, | + | |
- | (Select | + | (SELECT |
- | from TransHeader | + | |
- | where | + | |
- | coalesce(THTransHeaderID, | + | |
- | PercentComplete, | + | PercentComplete, |
- | (select max(EntryDateTime) | + | (SELECT MAX(EntryDateTime) |
- | from GL GL2 | + | |
- | where | + | |
- | from | + | FROM |
( | ( | ||
- | select coalesce(DivisionID, | + | SELECT COALESCE(DivisionID, |
- | TransactionID | + | TransactionID |
- | -sum(Amount) GLCredit | + | -SUM(Amount) GLCredit |
- | from GL | + | |
- | where GLAccountID = 24 | + | |
- | group by Coalesce(DivisionID, | + | GROUP BY COALESCE(DivisionID, |
- | having sum(Amount) | + | |
- | ) as A | + | ) AS A |
- | full join | + | |
+ | FULL JOIN | ||
( | ( | ||
- | select ID as THTransHeaderID, | + | SELECT |
- | OrderNumber, | + | OrderNumber, |
- | (PaymentTotal- (TotalPrice * PercentComplete/ | + | (PaymentTotal- (TotalPrice * PercentComplete/ |
- | StatusText, | + | StatusText, |
- | PercentComplete | + | PercentComplete |
- | from TransHeader | + | |
- | where StatusID | + | |
- | ) as B on A.GLTransHeaderID = B.THTransHeaderID | + | ) AS B ON A.GLTransHeaderID = B.THTransHeaderID |
- | where coalesce(A.GLCredit, | + | |
- | order by Division, OrderNumber | + | WHERE COALESCE(A.GLCredit, |
+ | |||
+ | ORDER BY Division, OrderNumber | ||
</ | </ | ||