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.
*/
select
[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],
[PORel].[PONum],
[PORel].[POLine],
[PORel].[PORelNum],
[PODetail_PartNum].[PartNum],
[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],
[PODetail].[POLine],
[PODetail].[PartNum],
[PODetail].[LineDesc],
[LatestDate].[Calculated_LatestDate]
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!
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.
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.
