We help IT Professionals succeed at work.

T-SQL: Calculations within a CTE

on
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              |
+-----------------+------------+---------------+----------+---------------+----------+-------------------+
``````
Comment
Watch Question

View Solution Only

Most Valuable Expert 2014
Awarded 2013

Commented:
Will this work for you?
``````select *
from (
select sum(coalesce(Quantity,1) * Labor_Cost) over() wanted_calc, *
from hierarchy
) d
where Item = '70-1010-BRX0014'
``````
Database Expert
Awarded 2016
Top Expert 2016

Commented:
What is the output of your CTE now ?

Commented:
Hi PortletPaul:

Your query works perfectly, for me!  Thank you!

Could you please give me a very brief explanation of how that query works?  :)

John
Most Valuable Expert 2014
Awarded 2013
Commented:
it works for the result you provided. The difficulty is that aggregating over a hierarchy can result in all sorts of multiplication.

The simplest way to investigate that query (and any multi-part query for that matter)  is to look at what each part produces. This is simple in this case

run this "part" query:

select sum(coalesce(Quantity,1) * Labor_Cost) over() wanted_calc, *
from hierarchy

look a the columns used in the formula in particular, look at wanted_calc (which is the sum of all rows)

now look at the full query result:

select *
from (
select sum(coalesce(Quantity,1) * Labor_Cost) over() wanted_calc, *
from hierarchy
) d
where Item = '70-1010-BRX0014'

consider carefully that I am use "sum(...) OVER(....)"

now ask questions if you need to