troubleshooting Question

Inflated Values Upon Running T-SQL Code

Avatar of Software Engineer
Software Engineer asked on
Microsoft SQL ServerSQL
2 Comments1 Solution103 ViewsLast Modified:
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

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

Bad-Results.xlsx
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros