Inner join causes duplicate rows

Epicor ERP 10
, ,
1

This is probably a simple SQL question, but I am at a loss. I have a stored procedure on an SSRS report for Scrap Details that returns 2 rows for each record. If I comment out the inner join below, then the results look correct. But, the JobMtl table is where we get the scrap cost, so I need it included. Can anyone tell me what’s wrong with this join, or what to add to it to make sure it does not return duplicate values? If need be I can post the whole sp, but this join is definitely the issue.

inner join 
		(select
			JobMtl.Company,
			JobMtl.JobNum,
			JobMtl.AssemblySeq,
			JobMtl.RelatedOperation,
			JobMtl.TotalCost,
			JobMtl.QtyPer,
			JobMtl.IssuedQty,
			JobMtl.PartNum,			
		CASE WHEN JobMtl.IssuedQty > 0 THEN (JobMtl.TotalCost / JobMtl.IssuedQty) * JobMtl.QtyPer ELSE 0 END as MtlUnitCost
		from erp.JobMtl
		where (JobMtl.Company = 'WSF') ) as Mtl on Mtl.Company = LaborDtl.Company
                            and Mtl.JobNum = LaborDtl.JobNum																			 
                            and Mtl.AssemblySeq = LaborDtl.AssemblySeq
2

Is it duplicate rows or x rows for every jobmtl in the assembly and x happens to be 2?

I think you may need to add Mtl.RelatedOperation = LaborDtl.OperSeq to your join. I’m on mobile and am not sure if OperSeq is the correct name, but there should be something like that on LaborDtl if not.

3

Actually, it’s probably the latter. I note that although JobMtl.PartNum is the same for both rows, JobAsmbl.PartNum is different.

JobMtl.RelatedOperation and LaborDtl.OprSeq contain different values. Adding that to the join returned 0.

4

Hmm, might need to see more of your query to understand what you’re going for in terms of the relationship between LaborDtl and JobMtl.

5 
select
	LaborDtl.Company,
	LaborDtl.ClockInDate,
	MONTH(LaborDtl.ClockInDate) as clockInMonth,
	YEAR(LaborDtl.ClockInDate) as clockInYear,
	EmpBasic.Name as ReportedBy,
	ResourceGroup.Description as ResGrpDesc,
	Resource.Description as ResDesc,
	LaborDtl.JobNum,
	LaborDtl.AssemblySeq,
	LaborDtl.OprSeq,
	JobAsmbl.PartNum,
	JobAsmbl.Description,
	LaborDtl.ScrapQty as Scrap_Res,
	LaborDtl.LaborQty,
	(LaborDtl.ScrapQty + LaborDtl.LaborQty) as TotalQty,
	Reason.Description as Reason,
	Mtl.MtlUnitCost,
	(Mtl.MtlUnitCost * LaborDtl.ScrapQty) as scrapCost_Mtl,
	(LaborDtl.LaborHrs) * (LaborDtl.LaborRate) AS LaborCost,
	(LaborDtl.BurdenHrs) * (LaborDtl.BurdenRate) AS BurdenCost,
	((LaborDtl.LaborHrs) * (LaborDtl.LaborRate))  + ((LaborDtl.BurdenHrs) * (LaborDtl.BurdenRate)) AS LbrBurCost,
	CASE
		WHEN (LaborDtl.ScrapQty + LaborDtl.LaborQty) = 0 THEN 0
		ELSE (((LaborDtl.LaborHrs) * (LaborDtl.LaborRate))  + ((LaborDtl.BurdenHrs) * (LaborDtl.BurdenRate))) / (LaborDtl.ScrapQty + LaborDtl.LaborQty) --Changed from Labor Qty only to Total Qty
	END AS LbrBurUnitCost,
	CASE
		WHEN (LaborDtl.ScrapQty + LaborDtl.LaborQty) = 0 THEN 0
		ELSE ((((LaborDtl.LaborHrs) * (LaborDtl.LaborRate)) + ((LaborDtl.BurdenHrs) * (LaborDtl.BurdenRate))) / (LaborDtl.ScrapQty + LaborDtl.LaborQty)) * LaborDtl.ScrapQty
	END AS scrapCost_LbrBur, --Labor + Burden Unit Cost
	CASE
		WHEN(LaborDtl.ScrapQty + LaborDtl.LaborQty) = 0 THEN 0
		ELSE (((((LaborDtl.LaborHrs) * (LaborDtl.LaborRate)) + ((LaborDtl.BurdenHrs) * (LaborDtl.BurdenRate))) / (LaborDtl.ScrapQty + LaborDtl.LaborQty)) * LaborDtl.ScrapQty) + (Mtl.MtlUnitCost * LaborDtl.ScrapQty)
	END AS Total_ScrapCost
from erp.LaborDtl
inner join 
		(select
			JobMtl.Company,
			JobMtl.JobNum,
			JobMtl.AssemblySeq,
			JobMtl.RelatedOperation,
			JobMtl.TotalCost,
			JobMtl.QtyPer,
			JobMtl.IssuedQty,
			CASE WHEN JobMtl.IssuedQty > 0 THEN (JobMtl.TotalCost / JobMtl.IssuedQty) * JobMtl.QtyPer ELSE 0 END as MtlUnitCost
		from erp.JobMtl
		where (JobMtl.Company = 'WSF') ) as Mtl on Mtl.Company = LaborDtl.Company
																			and Mtl.JobNum = LaborDtl.JobNum
																			and Mtl.AssemblySeq = LaborDtl.AssemblySeq
																			--and Mtl.RelatedOperation = LaborDtl.OprSeq
																			
left join Erp.Reason as Reason on 
	LaborDtl.Company = Reason.Company
	and LaborDtl.ScrapReasonCode = Reason.ReasonCode
	and Reason.ReasonType = 'S'
inner join Erp.JobOper AS JobOper ON LaborDtl.Company = JobOper.Company 
		and LaborDtl.JobNum = JobOper.JobNum 
		and LaborDtl.AssemblySeq = JobOper.AssemblySeq 
		and LaborDtl.OprSeq = JobOper.OprSeq 
left join Erp.JobOper_UD ON JobOper.SysRowID = JobOper_UD.ForeignSysRowID
		and (JobOper_UD.Date01 IS NOT NULL) 
inner join JobAsmbl AS JobAsmbl ON JobOper.Company = JobAsmbl.Company 
		and JobOper.JobNum = JobAsmbl.JobNum 
		and JobOper.AssemblySeq = JobAsmbl.AssemblySeq 
inner join Erp.EmpBasic ON LaborDtl.Company = EmpBasic.Company
		and LaborDtl.EmployeeNum = EmpBasic.EmpID
inner join Erp.Resource AS Resource ON LaborDtl.Company = Resource.Company 
		and LaborDtl.ResourceID = Resource.ResourceID 
inner join Erp.ResourceGroup AS ResourceGroup ON Resource.Company = ResourceGroup.Company 
		and Resource.ResourceGrpID = ResourceGroup.ResourceGrpID
where ScrapQty > 0
	and LaborDtl.ClockInDate >= '3/1/22'
	and (LaborDtl.Company = 'WSF')
order by LaborDtl.ClockInDate
6

In LaborDtl, you clock into an operation, the part you see is the job assembly part number.
When you are joining to JobMtl on Job / Asm, you are returning all the materials in that JobAsmbl / Assembly. You probably have 2 materials in the assemblies you are looking at.

Methods can assign related materials to operations, but it is not required, and when I look at my JobMtl table there is not a related operation sequence assigned.

You have 2 options I can see, either change your join to JobAsmbl ( assuming it has the fields you are looking for ), or join to a JobMtl that is grouped by Job / Assembly Seq, and has an aggregate function to roll up the field values you want.

2 Likes