# 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,
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
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')
),

(
MakeQuantities.Item_Location, MakeQuantities.Quantity, MakeQuantities.Assembly_Item_Key
LEFT OUTER JOIN MakeQuantities ON
where LaborAndOverheadCosts.Assembly_Item_Key IN ('70-1010-BRX0014', '1001311', '1001258')
)

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              |
+-----------------+------------+---------------+----------+---------------+----------+-------------------+
``````
Microsoft SQL ServerSQL

Last Comment
PortletPaul

8/22/2022 - Mon