Min Req. Date in BAQ

[Rick23S (Rick)]

I’m trying to get a BAQ to display a single row for each part number on order. Each part number may have multiple orders and releases, so I want the single row to be the earliest Req Date. Being a novice, I hoped it would be as simple as using min(Orders.OrderRel_ReqDate) for table criteria, but there’s more I don’t understand… How can I handle this?

[JasonMcD (Jason McDermott)]

Hey, Rick, welcome to the group.

You have to use a subquery.

• So Top Level will be probably the Part table
• Inner SubQuery will be OrderDtl joined to OrderRel
• In that subquery, group by everything except a calculated field for the min function as you said
• In the TopLevel, join the part table to the subquery.

Do you have the ICE Tools user guide from EpicWeb?

[Rick23S (Rick)]

Thanks for the reply! I could swear I did exactly what I just did 100 times and didn’t get the results I was looking for. Sure enough, it works fine now. I appreciate the help.

[Rick23S (Rick)]

Is there a way to do this using table criteria or subquery criteria? There are some other fields in OrderRel I want to add, but I’d like the keep the rows limited to rows with the earliest Req Date.

[JasonMcD (Jason McDermott)]

@Rick23S

There are some other fields in OrderRel I want to add, but I’d like the keep the rows limited to rows with the earliest Req Date.

I think this has been covered before, but I can’t seem to find the post.

You are asking, “Give me the earliest ReqDate for this part AND tell me what order/line it is for and the customer, etc.” Right?

Far as I know, the only way would be to add the OrderDtl table to the query again and join to it. Well, and OrderRel and whatever else and do another subquery. Here:

Join from Part to SQ1:

Join from SQ1 to SQ2:

SQ1 and grouping:

SQ2:

Results:

(PS, sort by part number…)

Of course, what if two orders are due the same day? Then you get duplicates. But, how else could you avoid that? Which order is the “right” one?

Also, this one comes up a lot:

Using Count() in BAQ - Incorrect Syntax

@tmcmullen you can also do Sub-Select SubQuery. Then you wouldnt need to do GROUP BY on your Top-Query. In a Nutshell: Make a Sub-Query but DO NOT Use it on the Designer On your TopLevel Query add the Sub-Query to a Calculated Field On the Sub-Query under SubQuery Criteria you can add WHERE Clause. Example: SubQuery1: [image] [image] SubQuery2: [image] [image] Result: [image] [image] Basically in a simpler view it does SELECT (SELECT count(*) FROM table WHERe...) as YourCalculate…

[Banderson (Brandon Anderson)]

@JasonMcD

Far as I know, the only way would be to add the OrderDtl table to the query again and join to it.

The other way to do that is use windowing functions to get a Row number in order of date then filtering the subquery by that row number. Look up windowing functions to see how that works.

[Rick23S (Rick)]

Excellent, thanks very much.

[JasonMcD (Jason McDermott)]

@Banderson

windowing functions

OK, there’s the smart answer I knew existed but had no idea what it was called. There needs to be like a hall of fame for mind-blowing answers. Or maybe it’s just me with just barely above zero SQL background. But I think this is not common knowledge around here.

Good stuff, I spent the last half hour reading a few of the posts on this.