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)
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!
TEAGLE_LatestPurchasePrice.baq (45.4 KB)