Stuck filtering a query… trying to figure out how to combine my SubQuery Criteria.
Right now my query pulls Order, Shipment, & Invoice Data.
I need to filter OUT any invoices sent to a list of customer accounts before a certain date.
Right now, I have (2) SubQuery Criteria:
( Customer.CustID IN a constant list AND InvcHead.InvoiceDate <= 2/20/2024 )
This results in a list of the records I want to filter OUT of my final results.
I’ve having trouble figuring out how to restructure these Criteria to filter OUT these results from the final query.
If I change it to:
( NOT Customer.CustID IN a constant list AND InvcHead.InvoiceDate <= 2/20/2024 )
Those accounts get filtered out all together, AND I only get records with Invoice Dates prior to 2/20/2024 on ALL accounts. So, it seems like its applying these criteria independently, even though I’ve grouped them in parenthesis.
I attempted using the “Having” toggle, but just end up with SQL errors, so if that’s the key, I’m not doing it correctly.
I think you are close with the “having” toggle. The trick for me has always been to set the grouping according to the “having” flags. Generally speaking, you have to have group by checked for fields you mark as “having”. But there is a bit more to it than that. Make sure your grouping is setup properly too.
The other (Grosser) option is to make 2 totally separate subqueries, one with the the customers in list and filtered dates, and the other with customers NOT in the same list. Then union those together.
It sucks because you have 2 subqueries to maintain, but it might be faster. Or it might not… but it’s something to try.
It would be interesting to see what index it is trying to use when it times out though.
Also, instead of filtering it in the subquery, can you filter it on the next level instead? Make the subquery with everything in it, then bring it into the next level and filter it there.
Yeah, the problem is I’m pushing this out to a BAQ report so if I change my query structure too much, I’ll probably have a lot more hoops to jump through.
I think it’s because there is no index on just the invoice date. So for it to find invoices with a cutoff date, its having to do a huge order by. Ordering is the biggest resource suck in a SQL query.
I’m going to try your earlier response… flag them with a calculated field and then perhaps I can filter them out. If not in the query results, I may be able to filter them on the SSRS side (as long as they don’t still impact the SSRS sums, which they sometimes do).
I have a 12:30 meeting… so I’ll attempt this afterwards and let you know.
Maybe if you change the order of your clauses (Not that OR Not this), instead of (Not this OR Not that)?
Just thinking that it might be able to short-circuit the evaluation - if the first comes out True, then it doesn’t have to check the second (which could also be why the AND runs faster)
There is also this criteria, that I’ve never actually used. lol. Sounds like you can get a list of the ones you want to exclude, then you can do a filter on the ones that show up in this list? Might be worth a shot.
Okay, so, still some funny stuff going on that I don’t understand the reason… but I got it to work.
I created a calculated field to flag the records I wanted to filter out.
I TRIED to then use that as a SubQuery Criteria, so my query results would only show “where” the “Ommit” calculated field was false (0). But again… this resulted in the query timing out.
So, I kyboshed the SubQuery Criteria. I had all my results, and a flag on all the rows I wanted to filter out, so I did everything else SSRS-side.
I had to add filters so those rows wouldn’t appear, and also had to add addition criteria to my sum calculations (because just because the rows are filtered out… their values are still in the result set). But… once I got through all that, I THINK I now have the final results on my report.
Thanks for the help, all. Thought this was gonna be much more straight forward than it was.
