So ive done this in the past and i cannot figure out why this is slipping my brain. I have done multiple BAQ’s where i will link the LaborDtl table as a sub-query and create the calculated field ( max(laborDtl.clockindate). Ill link up this sub-query with the top level query using Company, JobNum, and AsmblySeq and groupBy is checked off on all except the calculated field. I would repeat the same process but instead of clockindate i would pull max(laborDtl.Oprseq) to gain the last operation for that date. id now be able to see at a moments notice where any and all jobs for a certain part, customer, or date range where last located in the foundry and what date they were clocked in.
we never seemed to have a problem before but now im noticing the LastOpr is overriding the LastClockedInDate when i run the BAQ. So operation Cleaning = Seq# 200 and was clocked in today (2/8/2018) but operation Final = Seq#250 was clocked in on 2/3/2018. The report will show me that the last operation clocked in was Final because of its higher Seq# BUT itll show the most recent date.
What do i need to modify to have the LastClockedInDate be the deciding factor on which OprSeq pulls?
select
[LaborDtl2].[ReWork] as [LaborDtl2_ReWork],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[JobNum] as [JobHead_JobNum],
[SubQuery2].[Calculated_LastClockedInDate] as [Calculated_LastClockedInDate],
[SubQuery3].[Calculated_LastOpr] as [Calculated_LastOpr],
[OpMaster].[OpDesc] as [OpMaster_OpDesc],
[LaborDtl2].[LaborQty] as [LaborDtl2_LaborQty],
[JobOper].[QtyCompleted] as [JobOper_QtyCompleted],
[Part].[UnitPrice] as [Part_UnitPrice],
(( Part.UnitPrice * LaborDtl2.LaborQty )) as [Calculated_TotalCost],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[Part].[Artificial_Age_Code_c] as [Part_Artificial_Age_Code_c],
[Part].[Solution_HT_Code_c] as [Part_Solution_HT_Code_c],
[Customer].[Name] as [Customer_Name],
[Part].[Alloy_c] as [Part_Alloy_c],
[JobHead].[RevisionNum] as [JobHead_RevisionNum],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobHead].[CommentText] as [JobHead_CommentText]
from Erp.JobHead as JobHead
left outer join (select
[LaborDtl].[Company] as [LaborDtl_Company],
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[LaborDtl].[AssemblySeq] as [LaborDtl_AssemblySeq],
(max( LaborDtl.ClockInDate )) as [Calculated_LastClockedInDate]
from Erp.LaborDtl as LaborDtl
group by [LaborDtl].[Company],
[LaborDtl].[JobNum],
[LaborDtl].[AssemblySeq]) as SubQuery2 on
JobHead.Company = SubQuery2.LaborDtl_Company
and JobHead.JobNum = SubQuery2.LaborDtl_JobNum
right outer join (select
[LaborDtl1].[Company] as [LaborDtl1_Company],
[LaborDtl1].[JobNum] as [LaborDtl1_JobNum],
[LaborDtl1].[AssemblySeq] as [LaborDtl1_AssemblySeq],
(max( LaborDtl1.OprSeq )) as [Calculated_LastOpr]
from Erp.LaborDtl as LaborDtl1
group by [LaborDtl1].[Company],
[LaborDtl1].[JobNum],
[LaborDtl1].[AssemblySeq]) as SubQuery3 on
SubQuery3.LaborDtl1_Company = SubQuery2.LaborDtl_Company
and SubQuery3.LaborDtl1_JobNum = SubQuery2.LaborDtl_JobNum
and SubQuery3.LaborDtl1_AssemblySeq = SubQuery2.LaborDtl_AssemblySeq
right outer join Erp.JobOper as JobOper on
JobOper.Company = SubQuery3.LaborDtl1_Company
and JobOper.JobNum = SubQuery3.LaborDtl1_JobNum
and JobOper.AssemblySeq = SubQuery3.LaborDtl1_AssemblySeq
and JobOper.OprSeq = SubQuery3.Calculated_LastOpr
left outer join Erp.OpMaster as OpMaster on
JobOper.Company = OpMaster.Company
and JobOper.OpCode = OpMaster.OpCode
inner join Erp.LaborDtl as LaborDtl2 on
SubQuery3.LaborDtl1_Company = LaborDtl2.Company
and SubQuery3.LaborDtl1_JobNum = LaborDtl2.JobNum
and SubQuery3.LaborDtl1_AssemblySeq = LaborDtl2.AssemblySeq
and SubQuery3.Calculated_LastOpr = LaborDtl2.OprSeq
inner join Erp.JobProd as JobProd on
JobHead.Company = JobProd.Company
and JobHead.JobNum = JobProd.JobNum
and ( JobProd.ProdQty > JobProd.ShippedQty + JobProd.ReceivedQty )
inner join Erp.Part as Part on
JobProd.Company = Part.Company
and JobProd.PartNum = Part.PartNum
and ( Part.UserInteger1 > 0 )
left outer join Erp.OrderDtl as OrderDtl on
JobProd.Company = OrderDtl.Company
and JobProd.OrderNum = OrderDtl.OrderNum
and JobProd.OrderLine = OrderDtl.OrderLine
full outer join Erp.Customer as Customer on
OrderDtl.Company = Customer.Company
and OrderDtl.CustNum = Customer.CustNum
where (JobHead.JobClosed = FALSE and JobHead.JobFirm = True and JobHead.JobComplete = FALSE)
Your logic is good if this were directly in SQL. In the BAQ, I’m not sure
what tables you are joining, but be sure it is a fairly vague. Ah. For
the child to parent, join on just company and job num… try that out.
Okay, I have a dirty way of doing it, if you are interested. Essentially it is going to be 3 sub queries. The first (SubQuery1) is your labor detail, which is grouped on Job,Asm,Op, Clock In Date, and also a calculated field. It looks like the following:
The second query (SubQuery2) pulls in the above query, and groups on Job, Asm, and Clock in Date, and has another calculated field, which finds the Maximum value from the calculated field above. It looks like this:
Finally, the last query (SubQuery3) joins the two queries from above on Job,Asm,Clock In Date, and the 2 calculated fields. You can then display all the data, and it will only show the maximum operation for each date. The join in the final query looks like this:
by child parent are you referring to my jobhead and subquery2 in the picture above? im not the most well versed when it comes to the full on jargon of Epicor so please bare with me!
As for what tables im joining, BOTH sub-queries are LaborDtl. Sub2 is pulling max (labordtl.clockindate) and sub3 is pulling (LaborDtl.OprSeq) . I have those connected to each other with table relations of Company/JobNum/AssemblySeq.
Yes i think i understand it. the final subquery (3) is the one i should be connecting (using Job,Asm,Clock In Date, and the 2 calculated fields.) back into my top level, correct? and then selecting all of the information i need from there?
I have my top level query with all of my fields shown in the image i first posted except i removed the subqueries and replaced them with your Subquery3 (my 4th)
actually curious if this will work (probably not) Here is the exported BAQ I created to test it all out, See if it works for you, and it might help explain it a little more!
I was able to import it no issues there. I think im just misunderstanding how i actually use your 3 subqueries with the rest of my BAQ? Seeing the whole BAQ helped me understand its purpose but in my head i need to attach Subquery1 with my main BAQ SubQuery1 with all the other tables in it.
oh nice! didn’t know if that would work. Well, I don’t know what the rest of your query is doing, but this is just the part that finds the Max operation on any given date for a job and asm. You can then link that to any of your other tables to use it how you need.
Is the left side of the query above (SubQuery2 and SubQuery3) calculating the maximum operation currently?
alright ill try and attach your subquery3 to the rest of my baq and let you know.
if youre asking about my picture then yes those 2 subs are used to pull the max(clockindate) and max(oprseq)
I like the theory of yours so i will remove those two in favor of your method and re attach everything. I have a few meetings right now so i will update you as soon as ive given it a shot.
It did, I cannot run it because you have references i do not, but I fixed the sub queries and updated some references. Let me know if this works for you!
i was able to import it easily but when i try and run it, it times out. I tried to help it along by adding a part number criteria and although it spit out data it was a mess of it.
i noticed though that i havent actually added anything from the final subquery to my top level.
Sub1 is my top level
in Sub2 we create Calculated(MAX)
In Sub3 we create Calculated(m)
In Sub4 we link Sub2 and 3 and relate MAX = m
Should i be adding calculated(m) to my top level? or am i missing another step in between?
