Latest Purchase Price

Hi All,

Please can someone help me on this one? I’m trying to check the latest purchase date from PODetail and Rel I have used “max(column)” for both date and price fields but it’s still coming up with two dates this years and next years (2022)

Any suggestions?

Here is the BAQ Syntax and file.

 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
	[PODetail].[PONUM] as [PODetail_PONUM],
	[PODetail].[POLine] as [PODetail_POLine],
	[PODetail].[PartNum] as [PODetail_PartNum],
	[PODetail].[LineDesc] as [PODetail_LineDesc],
	(MAX(PODetail.OrderQty)) as [Calculated_SupplierQty],
	[LatestDate].[Calculated_LatestDate] as [Calculated_LatestDate],
	(MAX(PODetail.DocUnitCost)) as [Calculated_LatestPrice]
from Erp.PODetail as PODetail
inner join  (select distinct 
	[PORel].[Company] as [PORel_Company],
	[PORel].[PONum] as [PORel_PONum],
	[PORel].[POLine] as [PORel_POLine],
	[PORel].[PORelNum] as [PORel_PORelNum],
	(MAX(PORel.DueDate)) as [Calculated_LatestDate],
	[PODetail_PartNum].[PartNum] as [PODetail_PartNum_PartNum],
	[PODetail_PartNum].[VenPartNum] as [PODetail_PartNum_VenPartNum]
from Erp.PORel as PORel
left outer join Erp.PODetail as PODetail_PartNum on 
	PORel.Company = PODetail_PartNum.Company
	and PORel.PONum = PODetail_PartNum.PONUM
	and PORel.POLine = PODetail_PartNum.POLine
where (PORel.TranType = 'PUR-STK')
group by [PORel].[Company],
	[PODetail_PartNum].[VenPartNum])  as LatestDate on 
	PODetail.Company = LatestDate.PORel_Company
	and PODetail.PONUM = LatestDate.PORel_PONum
	and PODetail.POLine = LatestDate.PORel_PORelNum
	and PODetail.DueDate = LatestDate.Calculated_LatestDate
	and PODetail.VenPartNum = LatestDate.PODetail_PartNum_VenPartNum
	and PODetail.PartNum = LatestDate.PODetail_PartNum_PartNum
where (PODetail.DueDate > @FromDate)
group by [PODetail].[PONUM],
order by PODetail.PartNum

I have tried moving them around deleting PORel, adding extra relationships grouping by different columns but I can’t seem to get rid of the extra date!

TEAGLE_LatestPurchasePrice.baq (45.4 KB)

Many thanks

instead of using the “max” function, simply sort it by the date descending, and select the “first.or.default” which will give you the latest entry.

1 Like

Are you looking to get the max price and quantity? I’m not sure why you have those in your query.

Here’s what I would do. Get everything set up so that you see what you want to see with every row. (all the PO’s not just the last)

Then use a windowing function in a calculated field like this (I didn’t check the syntax so it may not be 100% right.

Row_Number () over (partition by PODetail.PartNum order by  POrel.DueDate desc)

This will get you the row numbers for every PO for a part, ordered by due date. Then in your subquery criteria, filter by that field = 1. That will get you the last PO that this part was on. Add any fields you need.

Does that make sense? If it wasn’t Friday at 5:00 I would show you an example, but that should get you close.

1 Like

How useful are those windowing functions!


How do we use First or Default in SQL (BAQ)

Unfortunately, this didn’t work as expected. We got duplicate rows and missing part numbers.

I’ve used this post in the past:

You were getting duplicates because you were filtering in PORel and joining to PODtl. If you had more than one PORel it was going to get you duplicated. Below is a modified query that get’s you the last PORel that a part was purchased on. You can add whatever fields that you need to the subquery and have them show up on your top level. The subquery criteria doesn’t work which is why you need 2 levels. This should show you how it works so that you can finish your query from there.

TEAGLE_LatestPurchasePrice_fixed.baq (54.9 KB)

If you are looking for max price, and max quantity, you will have to do more subqueries and then join on the part number in the top level.