Share
ReportI am writing my first SSRS report and it is for "vendors with no activity". My main query is below, but basically "no activity" is defined as no payments, purchases or POs since cutoffDate.
One of the criteria for the report is "Do not include a parent account with no activity if the child account has activity" (parent is defined as the InvoiceAccount, so parent would have accountNum = InvoiceAccount, child would have a different AccountNum but the same InvoiceAccount)
My question is, should I try to:
1. Build that into my query below (how?)
2. After building the temp table (tmpVendorsWithNoActivity) cycle through it again using this query:
while select * from tmpVendorsWithNoActivity
where tmpVendorsWithNoActivity.IsParent == "Yes"
join vendTable where vendTable.InvoiceAccount == tmpVendorsWithNoActivity.InvoiceAccount
&& vendTable.AccountNum != tmpVendorsWithNoActivity.InvoiceAccount
notexists join tmpVendorsWithNoActivity2
where tmpVendorsWithNoActivity2.AccountNum != vendTable.AccountNum
Original Query to Build Temp Table
while select crossCompany AccountNum, InvoiceAccount, NBDCMMSAllowInvoices
from vendTable
where vendTable.Blocked != CustVendorBlocked::All
notexists join * from vendTrans
where vendTable.AccountNum == vendTrans.AccountNum
&& vendTrans.TransDate > cutoffDateTrans
notExists join * from purchTable
where vendTable.AccountNum == purchTable.OrderAccount
&& purchTable.createdDateTime > cutoffDate
notExists join * from vendInvoiceJour
where vendTable.AccountNum == vendInvoiceJour.InvoiceAccount
&& (vendInvoiceJour.InvoiceDate) > cutoffDateTrans
*This post is locked for comments
I have the same question (0)
All responses (
Answers (
39
