T-SQL: Calculations within a CTE

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

Select allOpen in new window

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              |
+-----------------+------------+---------------+----------+---------------+----------+-------------------+

Select allOpen in new window