Inflated Values Upon Running T-SQL Code

Hello:

The first block of T-SQL code below is the CTE query that I'm working on and attached are its Results.

The Results are correct, since the code references only the following inventory items:  70-1010-BRX0014, 1001311, and 1001258.

But, when I comment out those references to "pull" all inventory items as shown in the second block of code, I get the attached "Bad Results".

I say "Bad Results", since what is showing in the "Total_Labor" and "Total_Overhead" fields are inflated.

These two fields are to represent the total labor and total overhead for the items shown in the "Item" field.

For example, in the "Bad Results" file, item 70-1010-BRX0014 shows total labor of 6054.371759 and total overhead of 8475.626353.

If you look in my "Results" file, though, you'll see that (for 70-1010-BRX0014) total labor is actually 6.754194 and total overhead is 9.454536.

Why does the code show inflated values for total labor and total overhead, when these three items are commented out of the code?

Thank you!  Much appreciated!

John

;WITH LaborAndOverheadCosts (Assembly_Item_Key, Labor_Cost, Overhead_Cost, Assembly_Location) AS
(
   SELECT 
      Assembly_Item_Key,
      Labor_Cost,
      Overhead_Cost, Assembly_Location
   FROM BM_View_SL_BOMS 
),

MakeQuantities (Make_Buy, Quantity, Item_Location, Assembly_Item_Key, Item_Key) AS
(
    SELECT Make_Buy, Quantity, Item_Location, BM_View_SL_BOMItems.Assembly_Item_Key, Item_Key
	FROM BM_View_SL_BOMItems 
	--LEFT OUTER JOIN BM_View_SL_BOMS ON
	--BM_View_SL_BOMItems.Item_Key = BM_View_SL_BOMS.Assembly_Item_Key 
	where Make_Buy = 'Make' 
	and BM_View_SL_BOMItems.Assembly_Item_Key IN (select Assembly_Item_Key from BM_View_SL_BOMS)
	and BM_View_SL_BOMItems.Assembly_Item_Key IN ('70-1010-BRX0014', '1001311', '1001258')
),

Hierarchy (Item, Labor_Cost, Overhead_Cost, Make_Buy, Item_Location, Quantity, Assembly_Item_Key) AS
(
SELECT LaborAndOverheadCosts.Assembly_Item_Key as Item, LaborAndOverheadCosts.Labor_Cost,
      LaborAndOverheadCosts.Overhead_Cost, MakeQuantities.Make_Buy, 
	  MakeQuantities.Item_Location, MakeQuantities.Quantity, MakeQuantities.Assembly_Item_Key
FROM LaborAndOverheadCosts
		LEFT OUTER JOIN MakeQuantities ON
		LaborAndOverheadCosts.Assembly_Item_Key = MakeQuantities.Item_Key 
	  where LaborAndOverheadCosts.Assembly_Item_Key IN ('70-1010-BRX0014', '1001311', '1001258')
)

--SELECT Item, Labor_Cost, Overhead_Cost, Make_Buy, Item_Location, Quantity, Assembly_Item_Key
--FROM Hierarchy
--ParentLabor (Labor_Cost) AS
--(
--SELECT Hierarchy.Labor_Cost 
--FROM Hierarchy where Make_Buy IS NULL
--)

--select Labor_Cost from ParentLabor
select *
from (
    select sum(coalesce(Quantity,1) * Labor_Cost) over() Total_Labor, 
	sum(coalesce(Quantity,1) * Overhead_Cost) over() Total_Overhead, 
	*
    from hierarchy
    ) d
where Item = '70-1010-BRX0014' and Assembly_Item_Key IS NULL

Select allOpen in new window

Results.xlsx

;WITH LaborAndOverheadCosts (Assembly_Item_Key, Labor_Cost, Overhead_Cost, Assembly_Location) AS
(
   SELECT 
      Assembly_Item_Key,
      Labor_Cost,
      Overhead_Cost, Assembly_Location
   FROM BM_View_SL_BOMS 
),

MakeQuantities (Make_Buy, Quantity, Item_Location, Assembly_Item_Key, Item_Key) AS
(
    SELECT Make_Buy, Quantity, Item_Location, BM_View_SL_BOMItems.Assembly_Item_Key, Item_Key
	FROM BM_View_SL_BOMItems 
	--LEFT OUTER JOIN BM_View_SL_BOMS ON
	--BM_View_SL_BOMItems.Item_Key = BM_View_SL_BOMS.Assembly_Item_Key 
	where Make_Buy = 'Make' 
	and BM_View_SL_BOMItems.Assembly_Item_Key IN (select Assembly_Item_Key from BM_View_SL_BOMS)
	--and BM_View_SL_BOMItems.Assembly_Item_Key IN ('70-1010-BRX0014', '1001311', '1001258')
),

Hierarchy (Item, Labor_Cost, Overhead_Cost, Make_Buy, Item_Location, Quantity, Assembly_Item_Key) AS
(
SELECT LaborAndOverheadCosts.Assembly_Item_Key as Item, LaborAndOverheadCosts.Labor_Cost,
      LaborAndOverheadCosts.Overhead_Cost, MakeQuantities.Make_Buy, 
	  MakeQuantities.Item_Location, MakeQuantities.Quantity, MakeQuantities.Assembly_Item_Key
FROM LaborAndOverheadCosts
		LEFT OUTER JOIN MakeQuantities ON
		LaborAndOverheadCosts.Assembly_Item_Key = MakeQuantities.Item_Key 
	  --where LaborAndOverheadCosts.Assembly_Item_Key IN ('70-1010-BRX0014', '1001311', '1001258')
)

--SELECT Item, Labor_Cost, Overhead_Cost, Make_Buy, Item_Location, Quantity, Assembly_Item_Key
--FROM Hierarchy
--ParentLabor (Labor_Cost) AS
--(
--SELECT Hierarchy.Labor_Cost 
--FROM Hierarchy where Make_Buy IS NULL
--)

--select Labor_Cost from ParentLabor
select *
from (
    select sum(coalesce(Quantity,1) * Labor_Cost) over() Total_Labor, 
	sum(coalesce(Quantity,1) * Overhead_Cost) over() Total_Overhead, 
	*
    from hierarchy
    ) d
where --Item = '70-1010-BRX0014' and 
Assembly_Item_Key IS NULL

Select allOpen in new window

Bad-Results.xlsx