You’re offline. This is a read only version of the page.
I'm writing a query to select all vendors, their last PO date, last Payment date and last Invoice date. Initially, I wrote a while select on all vendors (crosscompany, grouped by accountnum), then wrote select statements within the while statement to select the firstonly transaction from the 3 tables (PurchTable, VendTrans and VendInvoiceJour) order by [date field] desc.
This process takes 10 min with tiny amounts of data, and up to 3 hours in QA, so I built the query below in SQL and it runs fine. My question is, is it even possible to convert this kind of query to X++/QueryBuilder (I want to IGNORE all dataareaids):
SELECT DISTINCT t1.AccountNum, t1.InvoiceAccount, t1.VendGroup, t1.Party, t1.NBDCMMSAllowInvoices, t2.InvoiceDate, t3.CreatedDateTime, t4.TransDate FROM VendTable AS t1 LEFT JOIN (SELECT OrderAccount AS AccountNum, max(InvoiceDate) AS InvoiceDate FROM VendInvoiceJour GROUP BY OrderAccount) t2 ON t1.AccountNum = t2.AccountNum LEFT JOIN (SELECT OrderAccount AS AccountNum, max(CreatedDateTime) AS CreatedDateTime FROM PurchTable GROUP BY OrderAccount) t3 ON t1.AccountNum = t3.AccountNum LEFT JOIN (SELECT AccountNum, max(TransDate) AS TransDate FROM VendTrans GROUP BY AccountNum) t4 ON t1.AccountNum = t4.AccountNum
The tricky part is:
1. I only want 1 instance of each AccountNum (we have 26 companies, and vendors are replicated across all companies, so a single real-world vendor has 26 entries in the table, each with a different dataareaid).
2. I want the last PO/Invoice/Payment across ALL companies (so if Vendor A has 1 PO with date 01/01/2018 in Company A, but no POs in all other companies, I would want a single result for Vendor A with Last Po = 01/01/2018)
*This post is locked for comments
I have the same question (0)
Share
Report
All responses (
Answers (
4
