I am trying to write a BAQ in the PartTran table that will show the parts that do not show up with a specific date range. As simple as this sound, I am surprisingly not getting the results I want.
How can i pull this off?
Thanks!
You should be able to have a subquery pulling Part.PartNum, then an EXCEPT subquery pulling PartTran.PartNum with criteria on PartTran so it pulls all transactions inside your date range. The result of this will be a list of partnumbers that don’t have transactions in that range. Do you just need the list of P/Ns?
Where PartTran.TranDate < @StartDate AND PartTran.TranDate > @EndDate
That should be an or, not and and
. edit actually the ranges should be switched.
I just tried this and i think it worked:
Where PartTran.TranDate < @StartDate AND PartTran.TranDate < @EndDate
That’s going to give you everything less than the end date.
Maybe upload your BAQ. I don’t quite get what you are trying to do from your explanation.
PartNoTransInDate.baq (22.2 KB)
Here’s a sample BAQ that uses a left join. Anything in the top level with the subquery results coming back as empty are the rows that you want.
An Except query is going to be cleaner/better, but is sometimes harder to trust since you don’t see the empty cells.
Okay i think i got it this time.
This will give me transactions that do not show up within the date range.
Can you paste in your whole SQL statement?
select
[PartTran].[PartNum] as [PartTran_PartNum]
from Erp.PartTran as PartTran
where (PartTran.TranDate < @StartDate or PartTran.TranDate > @EndDate)
group by [PartTran].[PartNum]
order by PartTran.TranDateits the “order by”. Dang i knew it when i saw the code.
And that’s going to give you all transactions out of that date range, so that won’t tell you anything about what is or isn’t inside of that date range. (If I understand what you are trying to to). I’m pretty sure you want the list of everything inside that date range so you can look for which parts have a transaction in there and which don’t.
I just want everything that is outside of the date range.
think this through,
a= 1
a = 3
a = 5
b = 1
b=5
If you want a list of parts that don’t have 3 (my proxy for a date) but you do a search for everythign less than 2 and larger than 4 you get
a=1
a=5
b=1
b=5
It tells you nothing about whether a or b has 3. You’re looking for b right? A part that doesn’t have a transaction in that range?
Crap, you are right. So now im back to square one.
No, you’re almost there.
You actually want to start with a list of everything that IS in that range. So switch your <> around.
so if heres if your set
a= 1
a = 3
a = 5
b = 1
b=5
You end up with this
a=3
Now on different level you get all of the possible parts (use the part table) you get this.
a
b
then you join those 2 together but you use a left join so you get all of your possible parts.
all parts | parts in range
a ________a
b
Notice that b is empty in the second column. That signifies what you are looking for.
These are my parts that aren’t in my date range.
That’s the join you need to make.
2 Likes
Yup, that did it. Thanks!
“My [logic] is not as attuned as your’s”
1 Like
The other (probably better) way to do this is to use the except query. It’s a little pickier on the setup, but it works like this.
You except level is the PartTran table with the criteria and the grouping.
Your top level is just the Part table.
Both queries have to display the same number of field for this to work, and your order of tables need to be as shown.
When you are done, you just end up with this. Notice, those first two numbers are the one shown in my earlier screen shot. The except query displays only what you want, but, like I said, it’s a little harder to check/trust if you have a logic problem somewhere in your query so many people don’t like using it. (I rarely use it myself)
1 Like