Let rebuild the displayed fields for the main then. I would add one calculated field at a time until you get all 6 of them.

Get to this point then we can add back the other fields, I don’t think you want them apart of the group by.

select

[JobHead].[JobNum] as [JobHead_JobNum],

[JobHead].[PartNum] as [JobHead_PartNum],

[JobHead].[PartDescription] as [JobHead_PartDescription],

[JobHead].[RevisionNum] as [JobHead_RevisionNum],

[JobHead].[IUM] as [JobHead_IUM],

(sum(case when Varience_.PartTran2_Company is null then isnull(JobAsmbl1.Calculated_ToDateLabor,0)-isnull(PartTran1.Calculated_ToInvLabor,0) else isnull(JobAsmbl1.Calculated_ToDateLabor,0)-isnull(PartTran1.Calculated_ToInvLabor,0)-isnull(Varience_.Calculated_MfgVarLabor,0) end)) as [Calculated_WIPLabor],

(sum(case when Varience_.PartTran2_Company is null then isnull(JobAsmbl1.Calculated_ToDateBurden,0)-isnull(PartTran1.Calculated_ToInvBurden,0) else isnull(JobAsmbl1.Calculated_ToDateBurden,0)-isnull(PartTran1.Calculated_ToInvBurden,0)-isnull(Varience_.Calculated_MfgVarBurden,0) end)) as [Calculated_WIPBurden],

(sum(case when Varience_.PartTran2_Company is null then isnull(JobAsmbl1.Calculated_ToDateMaterial,0)-isnull(PartTran1.Calculated_ToInvMaterial,0) else isnull(JobAsmbl1.Calculated_ToDateMaterial,0)-isnull(PartTran1.Calculated_ToInvMaterial,0)-isnull(Varience_.Calculated_MfgVarMaterial,0) end)) as [Calculated_WIPMaterial],

(sum(case when Varience_.PartTran2_Company is null then isnull(JobAsmbl1.Calculated_ToDateSub,0)-isnull(PartTran1.Calculated_ToInvSub,0) else isnull(JobAsmbl1.Calculated_ToDateSub,0)-isnull(PartTran1.Calculated_ToInvSub,0)-isnull(Varience_.Calculated_MfgVarSub,0) end)) as [Calculated_WIPSub],

(sum(case when Varience_.PartTran2_Company is null then isnull(JobAsmbl1.Calculated_ToDateMtlBurden,0)-isnull(PartTran1.Calculated_ToInvMtlBurden,0) else isnull(JobAsmbl1.Calculated_ToDateMtlBurden,0)-isnull(PartTran1.Calculated_ToInvMtlBurden,0)-isnull(Varience_.Calculated_MfgVarMtlBurden,0) end)) as [Calculated_WIPMtlBurden]–,

–(sum(JobAsmbl1.Calculated_ToDateTotal-PartTran1.Calculated_ToInvTotal-Varience_.Calculated_MfgVarTotal)) as [Calculated_WIPTotal]

from Erp.JobHead as JobHead

left outer join Erp.JobProd as JobProd on

JobHead.Company = JobProd.Company

And

JobHead.JobNum = JobProd.JobNum

left outer join (select

[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],

[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],

[JobAsmbl].[Company] as [JobAsmbl_Company],

(sum(case when jobasmbl.AssemblySeq = 0 then (JobAsmbl.TLALaborCost + JobAsmbl.LLALaborCost) else 0 end)) as [Calculated_ToDateLabor],

(sum(case when jobasmbl.AssemblySeq = 0 then (JobAsmbl.TLABurdenCost + JobAsmbl.LLABurdenCost) else 0 end)) as [Calculated_ToDateBurden],

(sum(case when jobasmbl.AssemblySeq = 0 then (JobAsmbl.TLAMaterialCost + JobAsmbl.LLAMaterialCost) else 0 end)) as [Calculated_ToDateMaterial],

(sum(case when jobasmbl.AssemblySeq = 0 then (JobAsmbl.TLASubcontractCost + JobAsmbl.LLASubcontractCost) else 0 end)) as [Calculated_ToDateSub],

(sum(case when jobasmbl.AssemblySeq = 0 then (JobAsmbl.TLAMtlBurCost + JobAsmbl.LLAMtlBurCost) else 0 end)) as [Calculated_ToDateMtlBurden],

(ToDateLabor+ToDateBurden+ToDateMaterial+ToDateSub+ToDateMtlBurden) as [Calculated_ToDateTotal]

from Erp.JobAsmbl as JobAsmbl

group by [JobAsmbl].[JobNum],

[JobAsmbl].[PartNum],

[JobAsmbl].[Company]) as JobAsmbl1 on

JobHead.Company = JobAsmbl1.JobAsmbl_Company

And

JobHead.JobNum = JobAsmbl1.JobAsmbl_JobNum

And

JobHead.PartNum = JobAsmbl1.JobAsmbl_PartNum

left outer join (select

[PartTran].[Company] as [PartTran_Company],

[PartTran].[JobNum] as [PartTran_JobNum],

[PartTran].[PartNum] as [PartTran_PartNum],

(sum(PartTran.TranQty * PartTran.LbrUnitCost)) as [Calculated_ToInvLabor],

(sum(PartTran.TranQty * PartTran.BurUnitCost)) as [Calculated_ToInvBurden],

(sum(PartTran.TranQty * PartTran.MtlUnitCost)) as [Calculated_ToInvMaterial],

(sum(PartTran.TranQty * PartTran.SubUnitCost)) as [Calculated_ToInvSub],

(sum(PartTran.TranQty * PartTran.MtlBurUnitCost)) as [Calculated_ToInvMtlBurden]–,

(ToInvLabor+ToInvBurden+ToInvMaterial+ToInvSub+ToInvMtlBurden) as [Calculated_ToInvTotal]

from Erp.PartTran as PartTran

where (PartTran.TranType = ‘MFG-STK’)

group by [PartTran].[Company],

[PartTran].[JobNum],

[PartTran].[PartNum]) as PartTran1 on

JobHead.Company = PartTran1.PartTran_Company

And

JobHead.JobNum = PartTran1.PartTran_JobNum

And

JobHead.PartNum = PartTran1.PartTran_PartNum

left outer join (select

[PartTran2].[Company] as [PartTran2_Company],

[PartTran2].[JobNum] as [PartTran2_JobNum],

[PartTran2].[PartNum] as [PartTran2_PartNum],

(sum(isnull( PartTran2.TranQty,0) * isnull(PartTran2.LbrUnitCost,0) )) as [Calculated_MfgVarLabor],

(sum(isnull( PartTran2.TranQty,0) * isnull(PartTran2.BurUnitCost ,0) )) as [Calculated_MfgVarBurden],

(sum(isnull( PartTran2.TranQty,0) * isnull(PartTran2.MtlUnitCost ,0) )) as [Calculated_MfgVarMaterial],

(sum(isnull( PartTran2.TranQty,0) * isnull(PartTran2.SubUnitCost ,0) )) as [Calculated_MfgVarSub],

(sum(isnull( PartTran2.TranQty,0) * isnull(PartTran2.MtlBurUnitCost,0) )) as [Calculated_MfgVarMtlBurden]

,

(MfgVarLabor+MfgVarBurden+MfgVarMaterial+MfgVarSub+MfgVarMtlBurden) as [Calculated_MfgVarTotal]

from Erp.PartTran as PartTran2

where (PartTran2.TranType = ‘MFG-VAR’)

group by [PartTran2].[Company],

[PartTran2].[JobNum],

[PartTran2].[PartNum]) as Varience_ on

JobHead.Company = Varience_.PartTran2_Company

And

JobHead.JobNum = Varience_.PartTran2_JobNum

And

JobHead.PartNum = Varience_.PartTran2_PartNum

where (JobHead.JobNum = ‘MRP070007’)

group by [JobHead].[JobNum],

[JobHead].[PartNum],

[JobHead].[PartDescription],

[JobHead].[RevisionNum],

[JobHead].[IUM]