Hi everyone,
I am brand new to Epicor and the BAQ designer.
I can’t figure out how to recreate this query in the BAQ designer. It seems that I can’t join the subquery on the calculated field, LastDate.
Please advise. Thanks!
Can you send a couple screen shots of your table joins in the designer? I’ll take a closer look when I get back to my PC
What are you trying to get to? This looks simple and may not require a subquery.
I’m trying to get the JobNum of the most recently-closed job for each PartNum, so I figured that I had to do a subquery with Max(ClosedDate) and join it on ClosedDate=Max(ClosedDate).
I think you’re on the right track.
Keep in mind if you have multiple jobs closed on the same date, you will end up with multiple rows for a given part. We’d want to have something in the query to make sure we only get 1 (specifically the one you want).
1 Like
Might want to try windows function.
ROW_NUMBER() OVER(PARTITION BY jh.PartNum order by jh.ClosedDate desc ) as [Calculated_RowNum]
The Calculated_RowNum is the a new ranking of rows based on the ClosedDate of the jobhead record.
As Dan mentioned above, just be aware if there are multiple dates the order might be a bit mixed up. If you want, you could use SysRevID vs ClosedDate. That would be up to you.
You then could filter the results to show only Calculated_RowNum = 1. That would need to have another subquery in the BAQ editor as you cannot have windows functions in the where clause.
First Query
select jh.Company, jh.Plant, jh.PartNum, jh.ClosedDate, jh.JobNum,
(ROW_NUMBER() OVER(PARTITION BY jh.PartNum order by jh.ClosedDate desc )) as [Calculated_RowNum]
from JobHead as jh
where jh.JobClosed = 1
Second Query would be.
select *
from (
select jh.Company, jh.Plant, jh.PartNum, jh.ClosedDate, jh.JobNum,
(ROW_NUMBER() OVER(PARTITION BY jh.PartNum order by jh.ClosedDate desc )) as [Calculated_RowNum]
from JobHead as jh
where jh.JobClosed = 1) as partJobClosedDate
where partJobClosedDate. [Calculated_RowNum] = 1