BAQ How to get the Sales Order with Oldest Date

[Sire (Rebekka Silbermann)]

Hello, I need someones help. I am not sure what I am missing here.

I have been asked to create a BAQ containing the following:

1. List of Part Numbers on Hold
2. Type of Hold
3. ECR Number
4. Only the Latest Open Sales Order Number per part
5. Latest Open Sales Order Date per part
   Example: Order 1 for part A was placed 6/01/2020. Order 2 for part A was placed 6/20/2020. I only want the Sales Order that was entered first which would be Order 1.

I have attempted to use the calculation field and used Min(OrderDate), however I still return multiple Sales order for each part. I only want one, the oldest sales order date per part number.
Here is what I have tried.

I created a subquery shown below:

The fields I pulled for the Subquery:

Then I created an Top Level Query, per shown below:

The fields I pulled for the Top Level:

The Calculated fields I tried to make:

My failed results where the highlighted lines would be what I want displayed out of the group of results. Also not sure how to handle different orders for the same part that have the same date (Highlighted in Green):

Thank you ahead of time!

[bmgarver (Brian Garver)]

Sort the subquery by OrderDate ascending and pick the Top 1 only.

[Sire (Rebekka Silbermann)]

How do you tell the query to pick the top one only??

[Jamie_B (Jamie Bassett)]

[Sire (Rebekka Silbermann)]

Wow… Duh Bekka. Haha Thank you Jamie Bassett!

[Jamie_B (Jamie Bassett)]

This is the first time I’ve seen a question on this board that I actually knew the answer to, so thank you for the monday morning pick me up! lol

[Sire (Rebekka Silbermann)]

Still need HELP!!
I am still running into issues. If I use the Top function, then I only return one row of data. I thought I needed to separate it all out into independent sub-queries, but that is still not working. Any ideas how to get this to work?

Here is what I have. I have made 2 sub-queries and a top level query, as shown below.

I have tried to use the “Top” and “Distinct Top” on both the ‘Order Details’ sub-query and the top query. When applied to the Sub-query ‘Order Details’ it returns only 1 row. When applied to the ‘Part and Order Detail’ top query, it returns 0 rows.

Sub-Query 1.) Parts on Hold Details
SubQuery Options:

Phase Build:

Display Fields:

Calculated Fields:
Total Demand

On-Hand Qty

Sub-Query 2.) Order Details
SubQuery Options:

Phase Build:

Display Fields:

Top Query 3.) Part and Order Details
SubQuery Options:

I have tried with Ties and without. Also Top and Distinct top.

Phase Build:

Display Fields:

Everything I have tried, only returns this one row…

Please help me figure out what I am doing wrong… If you need more information, please let me know. I am happy to supply it.

[ckrusen (Calvin Krusen)]

Before I get to far into this… Any open order that called out the part? Or just open orders where that part is on an open line?

[skhayatt (Sam Khayatt)]

Try this query to start:

1. Join OrderDtl and OrderHed.
2. Add criterion to OrderDtl: OpenLine = 1
3. Create two calculated fields:
   (1) MinOrderDate = Min (OrderHed.OrderDate)
   (2) MinOrderNum = Min (OrderHed.OrderNum)
4. Display these fields
   OrderDtl.PartNum (GROUP BY THIS)
   MinOrderDate
   MinOrderNum

This should give you one open row per part containing the part number, the earliest order number and the date of the earliest order number.

You can then join the results of this query to the Part table to pull in the rest of the data.

(My assumption is that you want open lines, and not any open order containing this part if the line itself is not open)

[ckrusen (Calvin Krusen)]

Not sure that would handle the fact that the order dates and order numbers aren’t always sequential.

For example, two open orders for P/N WIDGET-001

1. Order 1000 was started on 6/1/20, (which defaults the order date to 6/1/20).
2. Order 1005 was started on 6/8/20, (which defaults the order date to 6/8/20).
3. Order 1000 has it’s order changed to 6/10/20.

Order 1005 is “older” as its OrderDate (6/8) is < 1000’s order date of 6/10.

Would your query show

PartNumber   MinOrderDate   MinOrderNum
==========   ============   ===========
WIDGET-001       6/8/2020          1000

Which are mismatched date and order numbers

[skhayatt (Sam Khayatt)]

You are right. Was not taking into account changed order dates, as we do not change them.

I guess you could decide to start either with oldest date or with smallest number and then bring in the other. Build it up, bit by bit.

[ckrusen (Calvin Krusen)]

You might have to combine the date and Ordernumber into a string so you first have something like:

Then on another subquery, select the MIN(OrdeDateNum) to get the oldest order order for that part

Then another level of sub-query to extract the Ordenum from the OrderDateNum string

[ckrusen (Calvin Krusen)]

This should get you started:

SubQuery1 (InnerSubQry)

Table Criteria: OrderDtl.OpenLine = 1

Display Fields:

1. OrderDtl_PartNum (GROUPED BY)
2. Calculated_OrderDateNum (GROUPED BY)
   = convert(varchar, (Datepart(Year,OrderHed.OrderDate)*10000+datepart(Month,OrderHed.OrderDate)*100+datepart(Day,OrderHed.OrderDate))) + '-'+ convert(varchar, OrderHed.OrderNum)

SubQuery2 (InnerSubQry)

Display Fields:

1. OrderDtl_PartNum (GROUPED BY)
2. Calculated_OldestOrderNum
   = Min(SubQuery1.Calculated_OrderDateNum)

SubQuery3 (TopLevel)

Display Fields:

1. OrderDtl_Partnum
2. Calculated_OldestOpenOrderDate
   = convert(date,substring(SubQuery2.Calculated_OldestOrderNum,1,8))
3. Calculated_OldestOpenOrderNum
   = convert(int, Substring(SubQuery2.Calculated_OldestOrderNum,10,10))

That will get a table on your top level that has one row per part number with the OrderNumber for the order with the oldest order date,

[Sire (Rebekka Silbermann)]

So it needs to be any part number that is on Eng hold AND has an open order against it. It can be a single stand alone order or one line on a multiple part order. Does that make sense?

[ckrusen (Calvin Krusen)]

Yes. What I’ve outlined above will give you a list of all the parts that are on an open order line, showing the order number with the oldest Order Date.

From there you can add you other tables to relate to the PartNum or the OderNum.

[kylepbsps (Kyle P)]

This might be kind of dirty but here is an alternate option. It will do a row count and sort by ASC or DESC. Then you can choose to only keep row #1.

On your subquery create calculated field.
ROW_NUMBER() OVER(PARTITION BY OrderDtl.PartNum ORDER BY OrderHed.OrderDate DESC)

On your Top Query, set your table criteria for the SubQuery to = constant of 1.