I have a simple BAQ that counts the number of bins for a part since the part is located in multiple bins.
It works OK but it also counts duplicate bin numbers i.e. if there are multiple lot numbers in the same bin, it will count the same bin more than once.
Is it possible to do this count where it ignores multiple occurrences?
In the image below, Subquery2 is the inner query that contains the “count” function and ignores some specific bins.
Subquery1 is the toplevel. i’ve selected a few parts to test.
here is the location. You should then be able to copy and paste like this.
select
[Part].[PartNum] as [Part_PartNum],
(left(Part.PartNum, (charindex('-',Part.PartNum)-1))) as [Calculated_BeforeDash],
(SUBSTRING(Part.PartNum,CHARINDEX('-',Part.PartNum)+1,LEN(Part.PartNum))) as [Calculated_afterDash],
(len(replace(Part.PartNum, '-', ''))) as [Calculated_LenWithoutDash],
(len(Part.PartNum)-1) as [Calculated_LenPartMin1],
(case when (Part.PartNum like '[0-9][0-9][0-9][-][0-9][0-9][0-9]') then 1 else 0 end) as [Calculated_formatcorrect]
from Erp.Part as Part
where (len(replace(Part.PartNum, '-', ''))) = Calculated.LenPartMin1 and Part.PartNum like '[0-9][0-9][0-9][-][0-9][0-9][0-9]'
select
[Part].[PartNum] as [Part_PartNum],
[SubQuery2].[Calculated_countBins] as [Calculated_countBins]
from Erp.Part as Part
inner join (select
[PartBin].[PartNum] as [PartBin_PartNum],
(count(*)) as [Calculated_countBins]
from Erp.PartBin as PartBin
where (PartBin.WarehouseCode = ‘main’ and not PartBin.BinNum in (‘desp’, ‘despatch’, ‘insp’, ‘spares’, ‘van’, ‘wip’, ‘zvan’))
group by [PartBin].[PartNum]) as SubQuery2 on
Part.PartNum = SubQuery2.PartBin_PartNum
where (Part.PartNum in (‘Part1’, ‘Part2’, ‘Part3’))
If you select * from PartBin where WarehouseCode is Main
What are you seeing? Do you see the duplicates? Are the lots important?
If not then you might need to do another sub query to once you have your part and bin list.
it would look like this.
select PartBinGroup.Company, PartBinGroup.PartNum, COUNT(*)
from
(select PartBin.Company, PartBin.PartNum, PartBin.BinNum
from Erp.PartBin as PartBin
where PartBin.WarehouseCode = 'main'
and not PartBin.BinNum in ('desp', 'despatch', 'insp', 'spares', 'van', 'wip', 'zvan')
group by PartBin.Company, PartBin.PartNum, PartBin.BinNum) as PartBinGroup
Group By PartBinGroup.Company, PartBinGroup.PartNum
Im sorry i didnt follow you clearly, where in the BAQ did you want me to select * from?
The only * i have is in the calculated field i.e. count(*).
In our E10 database, there are certain parts with different lot numbers in the same bin number, therefore on part tracker there are multiple duplicate fields.
For the part on the example below, I would expect the count result = 3, not = 4 (which is what i am getting).
select
[PartBinGroup].[PartBin_Company] as [PartBin_Company],
[PartBinGroup].[PartBin_PartNum] as [PartBin_PartNum],
(Count(PartBinGroup.PartBin_BinNum)) as [Calculated_BinCount]
from (select
[PartBin].[Company] as [PartBin_Company],
[PartBin].[PartNum] as [PartBin_PartNum],
[PartBin].[BinNum] as [PartBin_BinNum]
from Erp.PartBin as PartBin
group by [PartBin].[Company],
[PartBin].[PartNum],
[PartBin].[BinNum]) as PartBinGroup
group by [PartBinGroup].[PartBin_Company],
[PartBinGroup].[PartBin_PartNum]
