Return Most Recent Record in a BAQ

Question

I apologize that this has been asked before by many people. Unfortunately i have almost no knowledge of SQL and have been unsuccessful in making it work based on the previous questions that have been asked and answered.

I am tasked with creating a BAQ through Epicor10 and I need it to return 1 entry per part # based on the most recent receipt date. This is my query

    select 
    [PODetail].[PartNum] as [Part #],
    [Part].[PartDescription] as [Part Description],
    [PODetail].[PUM] as [Supplier UOM],
    [PODetail].[DocUnitCost] as [Unit Price],
    [RcvHead].[ReceiptDate] as [Receipt Date]
from Erp.Part as Part
inner join Erp.PODetail as PODetail on 
    Part.Company = PODetail.Company
    and Part.PartNum = PODetail.PartNum
cross join Erp.RcvHead as RcvHead

My results look like this.

Part Num    Description Supplier UOM    Unit Price  Receipt Date
100009      4x4-4/4   10" x 35"  EA     4.77        4/9/2020
100009      4x4-4/4   10" x 35"  EA     4.77        4/9/2020
100009      4x4-4/4   10" x 35"  EA     4.77        4/8/2020
100009      4x4-4/4   10" x 35"  EA     4.60        1/7/2020
100009      4x4-4/4   10" x 35"  EA     4.60        1/7/2020
100010      4x4-4/4   10" x 40"  EA     4.89        1/7/2020
100010      4x4-4/4   10" x 40"  EA     4.89        1/7/2020
100010      4x4-4/4   10" x 40"  EA     4.75        8/19/2019
100010      4x4-4/4   10" x 40"  EA     4.75        8/17/2019

When complete i want the results to be.

Part Num    Description Supplier UOM    Unit Price  Receipt Date
100009      4x4-4/4   10" x 35"  EA     4.77        4/9/2020
100010      4x4-4/4   10" x 40"  EA     4.89        1/7/2020

If someone could tell me what to put and where to put it in my code that would be extremely helpful. Again, sorry that this has been asked many times I just don't have enough knowledge to get it to work myself. Thank you.

Answer 1

To get the latest receipt date, you need to look at your Sort Order. Go to:

Query Builder > Display Fields > Sort Order

You will want to use Receipt Date and make sure its in descending order.

Then to make the BAQ return one record, go to the SubQuery Options Tab.

Query Builder > SubQuery Options

You can then specify that the BAQ only returns one row by changing the value in the dropdown called "Result Set Rows" to TOP, then specify below to only return one.