I need to be able to return the latest completed job per part number from the jobhead table. I’ve got this working with a BAQ that has a top level BAQ and one inner subquery BAQ, but it returns duplicates if the “latest completed date” is the same on multiple job records. From my research via Google I should be able to instead use a “window function” within my sql statement. However, I don’t know how to accomplish this in a BAQ format. I’ve included a link below to the example that I used. I’ve got the first solution working as described, but not the 2nd window function solution. Please advise.
So here is my current format with the top level and inner sub query version that has the issue with returning duplicates.
select
[JobHead1].[JobNum] as [JobHead1_JobNum],
[JobHead1].[PartNum] as [JobHead1_PartNum],
[JobHead1].[JobCompletionDate] as [JobHead1_JobCompletionDate]
from Erp.JobHead as JobHead1
inner join (select
[JobHead].[PartNum] as [JobHead_PartNum],
(max(JobHead.JobCompletionDate)) as [Calculated_LatestJobCompletionDate]
from Erp.JobHead as JobHead
where (JobHead.JobReleased = 1 and JobHead.JobComplete = 1 and JobHead.PartNum in (‘9032176’, ‘9033107’, ‘9034126’, ‘9034193’, ‘9046332’, ‘9046335’, ‘9046338’, ‘9046342’, ‘9051753’, ‘9051754’, ‘9051757’))
group by [JobHead].[PartNum]) as SubQuery1 on
JobHead1.PartNum = SubQuery1.JobHead_PartNum
and JobHead1.JobCompletionDate = SubQuery1.Calculated_LatestJobCompletionDate
These are the returned results. As you can see if the max date is the same on multiple records it returns both.
Job Part Completion Date
336522 9032176 2/24/2023 12:00:00 AM
DMR10384 9033107 8/15/2022 12:00:00 AM
335763 9034126 2/28/2023 12:00:00 AM
335974 9034126 2/28/2023 12:00:00 AM
324619 9034193 12/12/2022 12:00:00 AM
335771 9046332 3/8/2023 12:00:00 AM
DMR337738 9046335 2/22/2023 12:00:00 AM
330140 9046338 3/3/2023 12:00:00 AM
334114DEV 9046342 3/8/2023 12:00:00 AM
GRN336520 9051753 2/22/2023 12:00:00 AM
GRN336521 9051753 2/22/2023 12:00:00 AM
GRN336518 9051754 2/22/2023 12:00:00 AM
GRN336519 9051754 2/22/2023 12:00:00 AM
GRN336517 9051757 3/3/2023 12:00:00 AM
I understood that he has multiple jobs per part per day… thus resulting in duplicates for the same day…
Maybe combine with the LaborDtl where there is a Clockin/Clockout time… and a Complete flag.
Get the latest clockout date/Clockout time/complete = true ?
EDIT: Or create a date/time UD field whenever JobComplete is set to true, set the date/time.
Row_Number() over (partition by [JobHead].[PartNum] order byJobHead.JobCompletionDate desc)
Remove the group by on the subquery.
And that will give you number per row, and with ties, it will just pick one. Then when you bring the subquery in, filter to only allow when that calculated field equals 1.
(btw, I finally went to your link, and it’s the same exact thing that they are doing in the second example.)
Thanks Brandon, I just wasn’t sure how to turn their second example into BAQ form. If Epicor made it possible to copy and paste in a SQL statement directly into the BAQ Designer and it just auto created the GUI portion that would be amazing!!