Differences
This shows you the differences between two versions of the page.
control_sql_-_wip_and_built_integrity_check [2019/01/27 11:29] 127.0.0.1 external edit |
control_sql_-_wip_and_built_integrity_check [2019/02/05 11:30] admin |
||
---|---|---|---|
Line 24: | Line 24: | ||
<code sql> | <code sql> | ||
- | select | + | SELECT |
- | (select | + | (SELECT |
- | | + | |
- | | + | |
OrderNumber, | OrderNumber, | ||
- | GLStatusText | + | GLStatusText |
- | TransHeader.StatusText | + | TransHeader.StatusText |
TransHeader.SubTotalPrice, | TransHeader.SubTotalPrice, | ||
GLAmount, | GLAmount, | ||
OrdersDueAmount, | OrdersDueAmount, | ||
Account.CompanyName, | Account.CompanyName, | ||
- | TransHeader.Description | + | TransHeader.Description |
- | (select max(EntryDateTime) | + | (SELECT MAX(EntryDateTime) |
- | | + | |
- | | + | |
- | TransHeader.ID | + | TransHeader.ID |
- | + | ||
- | from | + | FROM |
( | ( | ||
- | | + | |
- | | + | |
( | ( | ||
- | | + | |
- | | + | |
- | SubTotalPrice | + | SubTotalPrice |
- | ' | + | ' |
- | AccountID | + | AccountID |
- | | + | |
- | | + | |
) A | ) A | ||
- | | + | |
( | ( | ||
- | | + | |
- | | + | |
- | ' | + | ' |
- | | + | |
- | AccountID | + | AccountID |
- | | + | |
- | | + | |
- | | + | |
- | | + | |
- | ) B on A.OrderTHID = B.GLTHID | + | ) B ON A.OrderTHID = B.GLTHID |
- | | + | |
+ | | ||
( | ( | ||
- | | + | |
- | -Sum(Amount) | + | -SUM(Amount) |
- | | + | |
- | | + | |
- | | + | |
- | | + | |
- | ) G on G.OrdersDueTHID = coalesce(A.OrderTHID, | + | ) G ON G.OrdersDueTHID = COALESCE(A.OrderTHID, |
- | + | ||
- | | + | |
- | | + | |
- | | + | |
- | | + | |
+ | | ||
+ | | ||
( | ( | ||
- | | + | |
- | | + | |
- | SubTotalPrice | + | SubTotalPrice |
- | ' | + | ' |
- | AccountID | + | AccountID |
- | | + | |
- | | + | |
) C | ) C | ||
- | | + | |
( | ( | ||
- | | + | |
- | | + | |
- | ' | + | ' |
- | | + | |
- | AccountID | + | AccountID |
- | | + | |
- | | + | |
- | | + | |
- | | + | |
- | ) D on C.OrderTHID = D.GLTHID | + | ) D ON C.OrderTHID = D.GLTHID |
- | + | ||
- | | + | |
( | ( | ||
- | | + | |
- | -Sum(Amount) | + | -SUM(Amount) |
- | | + | |
- | | + | |
- | | + | |
- | | + | |
- | ) K on K.OrdersDueTHID = coalesce(C.OrderTHID, | + | ) K ON K.OrdersDueTHID = COALESCE(C.OrderTHID, |
- | + | ||
- | | + | |
) E | ) E | ||
- | left Join TransHeader | + | |
- | left join Account | + | LEFT JOIN TransHeader |
+ | |||
+ | LEFT JOIN Account | ||
+ | |||
ORDER BY Division, OrderNumber | ORDER BY Division, OrderNumber | ||
</ | </ |