Sum function in BAQ returns bad results

Yahoo Archive
1
I figured it out. For every line that the inventory was returning, it had an entry for the WIP parts. The example that I showed had 4 entries for inventory parts and 1 for wip, so on all 4 of the lines it was returning a 1 for the WIP parts which added 3 parts. I had to write sub queries to get the totals for the Inventory parts to unique lines and another for the WIP totals to be on unique lines. Then on the top query add the two tables. Below is my final query that works. I might not need the "isnull" on both levels, but I just left if in there since it seems to work with it.

select
    [WhseBin].[WarehouseCode] as [WhseBin_WarehouseCode],
    [WhseBin].[BinNum] as [WhseBin_BinNum],
    (sum(isnull(SubQuery2.Calculated_BinTotalParts,0))+sum(isnull(SubQuery3.Calculated_WipTotal,0))) as [Calculated_TotalBin]
from Erp.WhseBin as WhseBin
left outer join  (select
    [PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
    [PartBin].[BinNum] as [PartBin_BinNum],
    (sum ( isnull(PartBin.OnhandQty,0))) as [Calculated_BinTotalParts]
from Erp.PartBin as PartBin
group by [PartBin].[WarehouseCode],
    [PartBin].[BinNum])  as SubQuery2 on
    WhseBin.WarehouseCode = SubQuery2.PartBin_WarehouseCode
And
    WhseBin.BinNum = SubQuery2.PartBin_BinNum

left outer join  (select
    [PartWip].[WareHouseCode] as [PartWip_WareHouseCode],
    [PartWip].[BinNum] as [PartWip_BinNum],
    (sum(isnull(PartWip.Quantity,0))) as [Calculated_WipTotal]
from Erp.PartWip as PartWip
group by [PartWip].[WareHouseCode],
    [PartWip].[BinNum])  as SubQuery3 on
    WhseBin.WarehouseCode = SubQuery3.PartWip_WareHouseCode
And
    WhseBin.BinNum = SubQuery3.PartWip_BinNum

group by [WhseBin].[WarehouseCode],
    [WhseBin].[BinNum]
2
Hi,

So I am trying to figure out why the sum function is not returning the expected result. Below are three queries. The first two display all bins and their respective quantities. The first if for inventory parts, the second is for wip parts since the tables are separate. The last query is to sum the two types together by bin number for a bin tracker dashboard that I am making. For some reason the summing BAQ does not return the total from the two queries that I look at to check them. In one example, a bin shows quantities of 41,15,18 and 7 in inventory and 1 in wip. That should total 82 parts. However, the summing query is returning 85. It seems like if there is only inventory and no wip, it comes back right. But if there is any wip it doesn't com back with the correct sum. If someone can see something that I am doing wrong, I would appreciate the help.

Thanks,
Brandon


Query for inventory parts

select
    [PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
    [PartBin].[BinNum] as [PartBin_BinNum],
    [PartBin].[PartNum] as [PartBin_PartNum],
    [PartBin].[OnhandQty] as [PartBin_OnhandQty]
from Erp.PartBin as PartBin

Query for wip parts.

select
    [PartWip].[WareHouseCode] as [PartWip_WareHouseCode],
    [PartWip].[BinNum] as [PartWip_BinNum],
    [PartWip].[PartNum] as [PartWip_PartNum],
    [PartWip].[Quantity] as [PartWip_Quantity],
    [PartWip].[JobNum] as [PartWip_JobNum]
from Erp.PartWip as PartWip


Query to sum the parts

select
    [WhseBin].[WarehouseCode] as [WhseBin_WarehouseCode],
    [WhseBin].[BinNum] as [WhseBin_BinNum],
    (sum(isnull( PartBin.OnhandQty,0 )) + sum(isnull( PartWip.Quantity,0))) as [Calculated_TotalParts]
from Erp.WhseBin as WhseBin
left outer join Erp.PartBin as PartBin on
    WhseBin.Company = PartBin.Company
And
    WhseBin.WarehouseCode = PartBin.WarehouseCode
And
    WhseBin.BinNum = PartBin.BinNum

left outer join Erp.PartWip as PartWip on
    WhseBin.WarehouseCode = PartWip.WareHouseCode
And
    WhseBin.BinNum = PartWip.BinNum
And
    WhseBin.Company = PartWip.Company

group by [WhseBin].[WarehouseCode],
    [WhseBin].[BinNum]