So I have this BAQ with UnionAll subquery, the top query for primary bins and 2nd subquery for specific secondary bins. [This 2nd subquery can be ignored]

I have put a filter where I only get 1 line showing the last value.

select

[Part].[PartNum] as [Part_PartNum],

(case

when SubQuery1.PartBin_BinNum = PlantWhse.PrimBin then ‘0’

else SubQuery1.PartBin_BinNum

end) as [Calculated_Bins],

[SubQuery1].[Calculated_OnHand] as [Calculated_OnHand],

[SubQuery1].[Calculated_MainBin] as [Calculated_MainBin]

from Erp.Part as Part

left outer join (select

[PartBin].[PartNum] as [PartBin_PartNum],

[PartBin].[BinNum] as [PartBin_BinNum],

(sum(PartBin.OnhandQty)) as [Calculated_OnHand],

(LAST_VALUE(PartBin.BinNum) over (Order by PartBin.PartNum)) as [Calculated_MainBin]

from Erp.PartBin as PartBin

where (PartBin.WarehouseCode = ‘main’ and not PartBin.BinNum in (’#default#’, ‘desp’, ‘insp’, ‘spares’, ‘van’, ‘wip’, ‘zvan’) and PartBin.BinNum is not null)

group by [PartBin].[PartNum],

[PartBin].[BinNum]) as SubQuery1 on

Part.PartNum = SubQuery1.PartBin_PartNum

left outer join Erp.PlantWhse as PlantWhse on

Part.Company = PlantWhse.Company

and Part.PartNum = PlantWhse.PartNum

and ( PlantWhse.WarehouseCode = ‘main’ )

where (Part.PartNum in (‘30030/g/st’))

and SubQuery1.PartBin_BinNum is not null and not (case

when SubQuery1.PartBin_BinNum = PlantWhse.PrimBin then ‘0’

else SubQuery1.PartBin_BinNum

end) = ‘0’ and SubQuery1.Calculated_MainBin = Calculated.Bins

union all

select

[Part_3].[PartNum] as [Part_3_PartNum],

(case

when SubQuery4.PartBin_3_BinNum = PlantWhse_3.PrimBin then ‘0’

else SubQuery4.PartBin_3_BinNum

end) as [Calculated_Bins_3],

[SubQuery4].[Calculated_OnHand_3] as [Calculated_OnHand_3],

(Bins_3) as [Calculated_MainBin_3]

from Erp.Part as Part_3

left outer join (select

[PartBin_3].[PartNum] as [PartBin_3_PartNum],

[PartBin_3].[BinNum] as [PartBin_3_BinNum],

(SUM(PartBin_3.OnhandQty)) as [Calculated_OnHand_3]

from Erp.PartBin as PartBin_3

where (PartBin_3.WarehouseCode = ‘main’ and PartBin_3.BinNum in (’#default#’, ‘desp’, ‘insp’, ‘spares’) and PartBin_3.BinNum is not null)

group by [PartBin_3].[PartNum],

[PartBin_3].[BinNum]) as SubQuery4 on

Part_3.PartNum = SubQuery4.PartBin_3_PartNum

left outer join Erp.PlantWhse as PlantWhse_3 on

Part_3.Company = PlantWhse_3.Company

and Part_3.PartNum = PlantWhse_3.PartNum

and ( PlantWhse_3.WarehouseCode = ‘main’ )

where (Part_3.PartNum in (‘30030/g/st’))

and not (case

when SubQuery4.PartBin_3_BinNum = PlantWhse_3.PrimBin then ‘0’

else SubQuery4.PartBin_3_BinNum

end) = ‘0’

group by [Part_3].[PartNum],

(case

when SubQuery4.PartBin_3_BinNum = PlantWhse_3.PrimBin then ‘0’

else SubQuery4.PartBin_3_BinNum

end),

[SubQuery4].[Calculated_OnHand_3],

(Bins_3)