Go Premium for a chance to win a PS4. Enter to Win

x
?

T-SQL: Calculations from CTEs

Posted on 2017-11-14
4
Low Priority
?
37 Views
Last Modified: 2017-11-15
Hello:

The first query below is the one that I need modified.  The second and third queries are the "BuyQuantities" and "MakeQuantities" CTEs that are embedded in the first query, respectively.

After the code for the second and third queries, I show the results.  I have, also, embedded those results in spreadsheets, in case you all find it easier to review from those.

The results for my first query are 66.071300.  This is incorrect.  It should be 43,429.7528.

This calculation should take the figures in the "Quantity" column of MakeQuantities and multiply them times the "Material_Cost" column of BuyQuantities where the "Item_Key" column of MakeQuantities equals the "Assembly_Item_Key" column of "BuyQuantities".  And, the amounts in the "Material_Cost" column of BuyQuantities should be added, even though item 70-1010-BRX0014 is not in the "Item_Key" column of MakeQuantities.

So, the formula should be:
(0.2390+0.2440+0.0600+0.3100) + (1*0.0092+0.1600) + (1*667.6300*65.0000+0.0473+0.0000+0.0018)

Please let me know how I can modify the first query to calculate this.

Thank you!

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

BuyQuantities (Make_Buy, Quantity, Item_Location, Assembly_Item_Key, Item_Key, Material_Cost) AS
(
    SELECT Make_Buy, Quantity, Item_Location, BM_View_SL_BOMItems.Assembly_Item_Key, Item_Key, Material_Cost
	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 = 'Buy' 
	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 (Make_Buy, Quantity, Item_Location, Assembly_Item_Key, Item_Key, Material_Cost) AS
(
    SELECT Make_Buy, Quantity, Item_Location, BM_View_SL_BOMItems.Assembly_Item_Key, Item_Key, Material_Cost
	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, BuyQuantities.Make_Buy, 
	  BuyQuantities.Item_Location, BuyQuantities.Quantity, BuyQuantities.Assembly_Item_Key
FROM LaborAndOverheadCosts
		LEFT OUTER JOIN BuyQuantities ON
		LaborAndOverheadCosts.Assembly_Item_Key = BuyQuantities.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
Material (Material, Assembly_Item_Key, Item_Key) AS
(
select *
from (select SUM(Material_Cost) as Material, Assembly_Item_Key, Item_Key
    from BuyQuantities
	GROUP BY Assembly_Item_Key, Item_Key
    ) d
where Assembly_Item_Key IN ('70-1010-BRX0014', '1001311', '1001258')
)

select DISTINCT sum(coalesce(MakeQuantities.Quantity,1) * Material) over() Total_Material
from Material
LEFT OUTER JOIN MakeQuantities on Material.Item_Key = MakeQuantities.Item_Key
LEFT OUTER JOIN BuyQuantities on MakeQuantities.Item_Key = BuyQuantities.Assembly_Item_Key
where Material.Assembly_Item_Key IN ('70-1010-BRX0014', '1001311', '1001258')

Open in new window



BuyQuantities:
   SELECT Make_Buy, Quantity, Item_Location, BM_View_SL_BOMItems.Assembly_Item_Key, Item_Key, Material_Cost
	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 = 'Buy' 
	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')

Open in new window


Make_Buy      Quantity      Item_Location      Assembly_Item_Key      Item_Key      Material_Cost
Buy      0.00069      BZRM      1001258      1000599      65
Buy      0.00379      BZRM      1001258      1000600      0.0473
Buy      0.00031      BZRM      1001258      1000654      0
Buy      0.00025      BZRM      1001258      1001037      0.0018
Buy      1      BZRM      1001311      1005142      0.0092
Buy      1      BZRM      1001311      1004628      0.16
Buy      1      BZRM      70-1010-BRX0014      1000778      0.239
Buy      1      BZRM      70-1010-BRX0014      1000779      0.244
Buy      1      BZRM      70-1010-BRX0014      1006921      0.06
Buy      1      BZRM      70-1010-BRX0014      1007705      0.31
Buy.xlsx


MakeQuantities:
SELECT Make_Buy, Quantity, Item_Location, BM_View_SL_BOMItems.Assembly_Item_Key, Item_Key, Material_Cost
	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')

Open in new window


Make_Buy      Quantity      Item_Location      Assembly_Item_Key      Item_Key      Material_Cost
Make      667.63      BZMFG      1001311      1001258      0.047
Make      1      BZMFG      70-1010-BRX0014      1001311      45.772
Make.xlsx
0
Comment
Question by:John Ellis
  • 2
  • 2
4 Comments
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 42369309
The first CTE, named LaborAndOverheadCosts, is not used within the second and third, it first reappears within the fourth query Hierarchy, so your understanding of this seems wrong:

The second and third queries are the "BuyQuantities" and "MakeQuantities" CTEs that are embedded in the first query, respectively.

I didn't deep dive into this yet, but it seems like this hints,  you have a fundamental misunderstanding you might solve yourself, once pointed to it. Also, notice The first CTE is based on the view BM_View_SL_BOMS while the follow-up "...Quantities" CTEs are based on BM_View_SL_BOMItems. So what are these view definitions? You might look at data, that really doesn't compare well and aggregates to wrong unexpected results.

To know what's really going on, I think some expert willing to dive in deeper will need to see the query definition of the views and also look at the data, this might better be solved in a Gig than by merely analyzing the queries.

Bye, Olaf.
0
 

Author Comment

by:John Ellis
ID: 42369852
Hi:

"The first CTE, named LaborAndOverheadCosts, is not used within the second and third, it first reappears within the fourth query Hierarchy, so your understanding of this seems wrong:"

On that point, the LaborAndOverheadCosts query is nothing that requires analysis, in this case.  I'm using the data returns derived from that CTE, in another project.

In any case, I do request that someone assist me with my needs in this case mentioned previously as follows:

"This calculation should take the figures in the "Quantity" column of MakeQuantities and multiply them times the "Material_Cost" column of BuyQuantities where the "Item_Key" column of MakeQuantities equals the "Assembly_Item_Key" column of "BuyQuantities".  And, the amounts in the "Material_Cost" column of BuyQuantities should be added, even though item 70-1010-BRX0014 is not in the "Item_Key" column of MakeQuantities.

 So, the formula should be:
 (0.2390+0.2440+0.0600+0.3100) + (1*0.0092+0.1600) + (1*667.6300*65.0000+0.0473+0.0000+0.0018)"


Thanks, a lot!  Much appreciated!

John
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 42369880
Maybe you clarify what you see as the first query, then. I see the definition of the LaborAndOverheadCosts CTE as first query. And "BuyQuantities" and "MakeQuantities" are not used within this first query.

Anyway, I'll make room for others getting at this. The core problem then doesn't seems to be where I think it was. Simply ignore me.

Bye, Olaf.
0
 

Author Comment

by:John Ellis
ID: 42369899
Hi:

When I say "first query", I'm referring to the first query that I posted here in this case.

And, indeed, BuyQuantities and MakeQuantities are within that query.

Thank you.

John
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question