troubleshooting Question

T-SQL: Calculations within a CTE

Avatar of Software Engineer
Software Engineer asked on
Microsoft SQL ServerSQL
4 Comments1 Solution137 ViewsLast Modified:
Hello:

Below is my CTE, and attached are the results of running the CTE in spreadsheet form.  (I have, also, embedded the results for those of you finding it easier to review them that way.)

As you may be able to tell, rows 2 and 3 of the results are items "within" the item in the final row, which is row 4.  You can see this, based on the relationship between the first column (Item) and the final column (Assembly_Item_Key).

Item 1001258 is within Assembly_Item_Key 1001311, and item 1001311 is within Assembly_Item_Key 70-1010-BRX0014.  Item 70-1010-BRX0014 is the parent, within this Hierarchy of items.  That's why the Assembly_Item_Key column is NULL, in row 4.

Anyway, I need to add a "select" clause at the end of this CTE that will calculate, as follows:

0.4125 + (1 * 4.59195) + (1 * 667.63 * 0.00058)

The following explains how I derived each element of this formula, from the results of my CTE:

0.4125 = Labor_Cost of 70-1010-BRX0014,
1 = Quantity of 1001311,
4.59195 = Labor_Cost of 1001311,
1 = Quantity of 1001311,
667.63 = Quantity of 1001258, and
0.00058 = Labor_Cost of 1001258.

Would someone please explain to me how to create this syntax?

Thank you, so much!

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

GoodResults_111017AM.xlsx

+-----------------+------------+---------------+----------+---------------+----------+-------------------+
|      Item       | Labor_Cost | Overhead_Cost | Make_Buy | Item_Location | Quantity | Assembly_Item_Key |
+-----------------+------------+---------------+----------+---------------+----------+-------------------+
| 1001258         |    0.00058 |       0.00082 | Make     | BZMFG         | 667.63   | 1001311           |
| 1001311         |    4.59195 |       6.42873 | Make     | BZMFG         | 1        | 70-1010-BRX0014   |
| 70-1010-BRX0014 |     0.4125 |        0.5775 | NULL     | NULL          | NULL     | NULL              |
+-----------------+------------+---------------+----------+---------------+----------+-------------------+
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
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 4 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