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

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

Open in new window

John EllisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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'

Open in new window

Pawan KumarDatabase ExpertCommented:
What is the output of your CTE now ?
John EllisAuthor 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
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.