Software Engineer
asked on
T-SQL: Iterative Calculations
Hello:
In ERP solutions, a "BOM" is a bill of material where the BOM is made up of other inventory items. And, those inventory items are made up of other items. So, each of these additional inventory items represent an additional "level" of the BOM.
I, therefore, need to have T-SQL conduct "iterative" calculations, where the programming tracks the costs at each level of the BOM--no matter how many levels the BOM has.
The first attachment shows the results of the following query:
The three fields called "Total Material Cost", "Total Labor Cost", and "Total Overhead Cost" are wrong. Each of those fields needs to conduct the calculations shown in cells B34, B32, and B33 found within the attached Excel spreadsheet called "Cost Breakdown".
Each of the six attached result sets that are shown in this case are derived from the following six "select" scripts:
The "Make_Buy" column of the first row of the first script tells you that item 1001311 is a Make item and is, therefore, another level of the BOM. This row, also, tells you that item 1001311 has a Quantity of 1 for BOM 70-1010-BRX0014.
Likewise, The "Make_Buy" column of the first row of the second script tells you that item 1001258 is a Make item and is, therefore, another level of the BOM. This row, also, tells you that item 1001258 has a Quantity of 667.63 for 1001311.
The final three scripts show the Labor and Overhead Costs of 70-1010-BRX0014, 1001311, 1001258.
Rows 2 - 5 and column Item_Key of the first script show the "Buy" items that make up 70-1010-BRX0014. These items are seen, as well, in cells A5 - A8 of the attached spreadsheet.
Rows 2 - 3 and column Item_Key of the second script show the "Buy" items that make up 1001311. These items are seen, too, in cells B17 - B18 of the attached spreadsheet.
Rows 1 - 4 and column Item_Key of the third script show the "Buy" items that make up 1001258. These items are seen, additionally, in cells C27 - C30 of the attached spreadsheet.
These first three scripts, then, show Material Costs. ("Buy" items make up Material Costs.)
Again, I need for T-SQL to conduct the calculations that are shown within cells B32, B33, and B34 of the attached Excel spreadsheet and place those results into "Total Labor Costs", "Total Overhead Costs", and "Total Material Costs", respectively, for the first script (query) that I mentioned in this case.
How do I go about producing such syntax?
Thank you! Much appreciated!
John
Report.rpt
70-1010-BRX0014-Cost-Breakdown_.xlsx
Quantity_70-1010-BRX0014.rpt
Quantity_1001258.rpt
Quantity_1001311.rpt
Labor-and-Overhead_70-1010-BRX0014.rpt
Labor-and-Overhead_1001258.rpt
Labor-and-Overhead_1001311.rpt
In ERP solutions, a "BOM" is a bill of material where the BOM is made up of other inventory items. And, those inventory items are made up of other items. So, each of these additional inventory items represent an additional "level" of the BOM.
I, therefore, need to have T-SQL conduct "iterative" calculations, where the programming tracks the costs at each level of the BOM--no matter how many levels the BOM has.
The first attachment shows the results of the following query:
select BM_View_SL_BOMS.Assembly_Item_Key as [Assembly Item Key], IV00101.ITEMDESC as [Item Description],
BM_View_SL_BOMS.Assembly_Location as [Assembly Location], BM_View_SL_BOMS.Entry_Date as [Entry Date],
BM_View_SL_BOMS.Status as [Status], BM_View_SL_BOMS.BOMUOM as [BOMUOM], BM_View_SL_BOMS.Material_Cost as [Total Material Cost],
BM_View_SL_BOMS.Labor_Cost as [Total Labor Cost], BM_View_SL_BOMS.Overhead_Cost as [Total Overhead Cost],
IV00101.STNDCOST as [Standard Cost], IV00101.CURRCOST as [Current Cost]
from BM_View_SL_BOMS INNER JOIN
IV00101 on
BM_View_SL_BOMS.Assembly_Item_Key = IV00101.ITEMNMBR
where BM_View_SL_BOMS.Assembly_Item_Key like '70-%'The three fields called "Total Material Cost", "Total Labor Cost", and "Total Overhead Cost" are wrong. Each of those fields needs to conduct the calculations shown in cells B34, B32, and B33 found within the attached Excel spreadsheet called "Cost Breakdown".
Each of the six attached result sets that are shown in this case are derived from the following six "select" scripts:
--material costs and quantity of each "make" item
select * from BM_View_SL_BOMItems where Assembly_Item_Key = '70-1010-BRX0014'
select * from BM_View_SL_BOMItems where Assembly_Item_Key = '1001311'
select * from BM_View_SL_BOMItems where Assembly_Item_Key = '1001258'
--labor and overhead costs
select * from BM_View_SL_BOMS where Assembly_Item_Key = '70-1010-BRX0014'
select * from BM_View_SL_BOMS where Assembly_Item_Key = '1001311'
select * from BM_View_SL_BOMS where Assembly_Item_Key = '1001258'The "Make_Buy" column of the first row of the first script tells you that item 1001311 is a Make item and is, therefore, another level of the BOM. This row, also, tells you that item 1001311 has a Quantity of 1 for BOM 70-1010-BRX0014.
Likewise, The "Make_Buy" column of the first row of the second script tells you that item 1001258 is a Make item and is, therefore, another level of the BOM. This row, also, tells you that item 1001258 has a Quantity of 667.63 for 1001311.
The final three scripts show the Labor and Overhead Costs of 70-1010-BRX0014, 1001311, 1001258.
Rows 2 - 5 and column Item_Key of the first script show the "Buy" items that make up 70-1010-BRX0014. These items are seen, as well, in cells A5 - A8 of the attached spreadsheet.
Rows 2 - 3 and column Item_Key of the second script show the "Buy" items that make up 1001311. These items are seen, too, in cells B17 - B18 of the attached spreadsheet.
Rows 1 - 4 and column Item_Key of the third script show the "Buy" items that make up 1001258. These items are seen, additionally, in cells C27 - C30 of the attached spreadsheet.
These first three scripts, then, show Material Costs. ("Buy" items make up Material Costs.)
Again, I need for T-SQL to conduct the calculations that are shown within cells B32, B33, and B34 of the attached Excel spreadsheet and place those results into "Total Labor Costs", "Total Overhead Costs", and "Total Material Costs", respectively, for the first script (query) that I mentioned in this case.
How do I go about producing such syntax?
Thank you! Much appreciated!
John
Report.rpt
70-1010-BRX0014-Cost-Breakdown_.xlsx
Quantity_70-1010-BRX0014.rpt
Quantity_1001258.rpt
Quantity_1001311.rpt
Labor-and-Overhead_70-1010-BRX0014.rpt
Labor-and-Overhead_1001258.rpt
Labor-and-Overhead_1001311.rpt
Can you pls provide input and output in an excel.?
ASKER
Hi:
Below are all of the corresponding files that I attached, yesterday. This time, all files are in Excel format--not .rpt format.
Please. I need your help!
Thanks! Again, much appreciated!
John
Report.xlsx
70-1010-BRX0014-Cost-Breakdown_.xlsx
Quantity_70-1010-BRX0014.xlsx
Quantity_1001311.xlsx
Quantity_1001258.xlsx
Labor-and-Overhead_70-1010-BRX0014.xlsx
Labor-and-Overhead_1001311.xlsx
Labor-and-Overhead_1001258.xlsx
Below are all of the corresponding files that I attached, yesterday. This time, all files are in Excel format--not .rpt format.
Please. I need your help!
Thanks! Again, much appreciated!
John
Report.xlsx
70-1010-BRX0014-Cost-Breakdown_.xlsx
Quantity_70-1010-BRX0014.xlsx
Quantity_1001311.xlsx
Quantity_1001258.xlsx
Labor-and-Overhead_70-1010-BRX0014.xlsx
Labor-and-Overhead_1001311.xlsx
Labor-and-Overhead_1001258.xlsx
You most definitely want to use a CTE for a recursive hierarchy like a BOM. The general structure would be:
;WITH Recipe (RootItemCode, Code, Father, LineNum, ParentLineNum, [Level], ParentQty, ChildQty) AS
(
/* this is the "root" or original parent definition -- the item that is being made by the BOM */
SELECT t0.Code AS RootItemCode,
t0.Code,
t0.Code AS Father,
-1 AS LineNum,
-1 AS ParentLineNum,
0 AS [Level],
t0.Quantity AS ParentQty,
CAST(NULL AS DECIMAL(19, 6)) AS ChildQty
FROM #oitt t0
WHERE t0.Code = @Item
UNION ALL
-- Recursive member definition
SELECT r.RootItemCode,
t1.Code,
t1.Father,
t1.LineNum,
r.LineNum AS ParentLineNum,
[Level] + 1,
t1.Quantity AS ParentQty,
CAST(t1.Quantity AS DECIMAL(19, 6)) AS ChildQty
FROM #itt1 t1 /* this is the table that has the ingredients for the main item */
INNER JOIN Recipe AS r ON r.Code = t1.Father
)
SELECT * from RecipeASKER
Thank you, for the information!
I do, however, feel like I need additional help on this.
Can someone please provide at least a few more "hints"? It would be appreciated!
Thank you!
John
I do, however, feel like I need additional help on this.
Can someone please provide at least a few more "hints"? It would be appreciated!
Thank you!
John
John,
In which one if the 9 files will one find the hierarchy data? (The parent child relationships.) Are the column names really so obvious that we won't make mistakes?
You are overwhelming us a tad. Do it in bite sized chunks, I for one cannot swallow the whole elephant in a single gulp.
In which one if the 9 files will one find the hierarchy data? (The parent child relationships.) Are the column names really so obvious that we won't make mistakes?
You are overwhelming us a tad. Do it in bite sized chunks, I for one cannot swallow the whole elephant in a single gulp.
ASKER
Hi PortletPaul:
The file showing the hierarchy is the "70-1010-BRX0014-Cost-Brea
If this helps, someone can simply give me additional hints on how to do this.
Thanks, again!
John
The file showing the hierarchy is the "70-1010-BRX0014-Cost-Brea
If this helps, someone can simply give me additional hints on how to do this.
Thanks, again!
John
ASKER
Hi:
Perhaps, if you all could help me iron out my query below, I can move forward with the remainder of the work.
When I say "iron out", here's what I'm getting at. Please refer to cell D21 of the first spreadsheet that I have attached. I need this figure of 667.63 for Item_Key 1001258 of that spreadsheet.
You'll find 667.63 in cell E3 of the second spreadsheet that I have attached. This spreadsheet is the results of running the query below.
Based on what I have mentioned previously in this case, how do I derive 667.73 for Assembly_Item_Key 1001258 within my results for this query?
Thank you!
John
70-1010-BRX0014-Cost-Breakdown_.xlsx
CTE.xlsx
Perhaps, if you all could help me iron out my query below, I can move forward with the remainder of the work.
When I say "iron out", here's what I'm getting at. Please refer to cell D21 of the first spreadsheet that I have attached. I need this figure of 667.63 for Item_Key 1001258 of that spreadsheet.
You'll find 667.63 in cell E3 of the second spreadsheet that I have attached. This spreadsheet is the results of running the query below.
Based on what I have mentioned previously in this case, how do I derive 667.73 for Assembly_Item_Key 1001258 within my results for this query?
Thank you!
John
;WITH LaborAndOverheadCosts (Assembly_Item_Key, Labor_Cost, Overhead_Cost, Assembly_Location) AS
(
SELECT DISTINCT
Assembly_Item_Key,
Labor_Cost,
Overhead_Cost, Assembly_Location
FROM BM_View_SL_BOMS
),
MakeQuantities (Make_Buy, Quantity, Labor_Cost, Item_Key, Item_Location) AS
(
SELECT DISTINCT
Make_Buy, Quantity, Labor_Cost, Item_Key, Item_Location
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.Item_Location = BM_View_SL_BOMS.Assembly_Location
)
SELECT DISTINCT LaborAndOverheadCosts.Assembly_Item_Key, LaborAndOverheadCosts.Labor_Cost,
LaborAndOverheadCosts.Overhead_Cost, MakeQuantities.Make_Buy, MakeQuantities.Quantity, MakeQuantities.Labor_Cost
FROM LaborAndOverheadCosts
LEFT OUTER JOIN MakeQuantities ON
LaborAndOverheadCosts.Assembly_Item_Key = MakeQuantities.Item_Key and LaborAndOverheadCosts.Assembly_Location = MakeQuantities.Item_Location
where LaborAndOverheadCosts.Assembly_Item_Key IN ('70-1010-BRX0014', '1001311', '1001258')70-1010-BRX0014-Cost-Breakdown_.xlsx
CTE.xlsx
I have no idea how you will choose line 4 of the second table from all the other similar rows. What makes that row special and relevant?
to choose that specific row (which is virtually the same as many others) there HAS TO SOMETHNG to grasp on to (that we cannot see)
| Assembly_Item_Key | Labor_Cost | Overhead_Cost |
|-------------------|------------|---------------|
| 1001311 | 4.1745 | 5.8443 |
| | | |
| Item_Key | Make_Buy | MaterialCost |
| 1005142 | Buy | 0.0092 |
| 1004628 | Buy | 0.16 |
| | | |
| Item_Key | Make_Buy | Quantity |
| 1001258 | Make | 667.63 || Assembly_Item_Key | Labor_Cost | Overhead_Cost | Make_Buy | Quantity | Labor_Cost |
|-------------------|------------|---------------|----------|----------|------------|
| 1001258 | 0.00058 | 0.00082 | Make | 617.91 | 0.00058 |
| 1001258 | 0.00058 | 0.00082 | Make | 667.63 | 0.00058 |
| 1001258 | 0.00058 | 0.00082 | Make | 739.92 | 0.00058 |
| 1001258 | 0.00058 | 0.00082 | Make | 801.16 | 0.00058 |
| 1001258 | 0.00058 | 0.00082 | Make | 984.47 | 0.00058 |
| 1001258 | 0.00058 | 0.00082 | Make | 1065.76 | 0.00058 |
| 1001258 | 0.00058 | 0.00082 | Make | 1235.8 | 0.00058 |
| 1001258 | 0.00058 | 0.00082 | Make | 1335.26 | 0.00058 |
| 1001258 | 0.00058 | 0.00082 | Make | 1489.3 | 0.00058 |
| 1001258 | 0.00058 | 0.00082 | Make | 1613.44 | 0.00058 |
| 1001258 | 0.00058 | 0.00082 | Make | 1787.2 | 0.00058 |
| 1001311 | 4.59195 | 6.42873 | Make | 1 | 4.59195 |
| 70-1010-BRX0014 | 0.4125 | 0.5775 | NULL | NULL | NULL |to choose that specific row (which is virtually the same as many others) there HAS TO SOMETHNG to grasp on to (that we cannot see)
ASKER
Hi There:
The Cost Breakdown spreadsheet shows that 667.63 is the required figure. Also, the previous files and updates that I made to this case should help, on this.
John
The Cost Breakdown spreadsheet shows that 667.63 is the required figure. Also, the previous files and updates that I made to this case should help, on this.
John
No. They do not help at all.
What would be helpful if you answered my questions. How can you select THAT row containing THAT value?
There MUST be some other columns available that makes this possible.
What would be helpful if you answered my questions. How can you select THAT row containing THAT value?
There MUST be some other columns available that makes this possible.
ASKER
Hi:
I think that I have something.
Please review my CTE query below and its attached results in Excel.
I want only rows 9, 45, and 46 of the spreadsheet to appear for me, upon running my query. You will notice that the last Assembly_Item_Key column in row 9 is the same as the first Assembly_Item_Key column in row 45.
This is exactly the type of data that I'm trying to capture. But, I'm having trouble placing this logic inside of my query.
If you all can figure this out for me, I think that I'm "good".
Thank you!
John
CTE2.xlsx
I think that I have something.
Please review my CTE query below and its attached results in Excel.
I want only rows 9, 45, and 46 of the spreadsheet to appear for me, upon running my query. You will notice that the last Assembly_Item_Key column in row 9 is the same as the first Assembly_Item_Key column in row 45.
This is exactly the type of data that I'm trying to capture. But, I'm having trouble placing this logic inside of my query.
If you all can figure this out for me, I think that I'm "good".
Thank you!
John
CTE2.xlsx
;WITH LaborAndOverheadCosts (Assembly_Item_Key, Labor_Cost, Overhead_Cost, Assembly_Location) AS
(
SELECT DISTINCT
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 DISTINCT 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)
)
SELECT DISTINCT LaborAndOverheadCosts.Assembly_Item_Key, 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')ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi PortletPaul:
Below are the results. Thank you, for looking into this!
I did paste the results into a code block, as you had asked. But, in case you have trouble misinterpreting anything, I did attach another spreadsheet. (I'm not sure which is easier for you to look at.)
John
CTE_ExpertsExchange.xlsx
Below are the results. Thank you, for looking into this!
I did paste the results into a code block, as you had asked. But, in case you have trouble misinterpreting anything, I did attach another spreadsheet. (I'm not sure which is easier for you to look at.)
John
Assembly_Item_Key Assembly_Item_Key Labor_Cost Overhead_Cost Make_Buy Item_Location Quantity
1001258 1001264 0.00058 0.00082 Make BZMFG 617.91
1001258 1001265 0.00058 0.00082 Make BZMFG 739.92
1001258 1001266 0.00058 0.00082 Make BZMFG 984.47
1001258 1001267 0.00058 0.00082 Make BZMFG 1235.8
1001258 1001273 0.00058 0.00082 Make BZMFG 617.91
1001258 1001274 0.00058 0.00082 Make BZMFG 739.92
1001258 1001275 0.00058 0.00082 Make BZMFG 984.47
1001258 1001276 0.00058 0.00082 Make BZMFG 1235.8
1001258 1001277 0.00058 0.00082 Make BZMFG 1489.3
1001258 1001279 0.00058 0.00082 Make BZMFG 617.91
1001258 1001280 0.00058 0.00082 Make BZMFG 739.92
1001258 1001281 0.00058 0.00082 Make BZMFG 984.47
1001258 1001282 0.00058 0.00082 Make BZMFG 1235.8
1001258 1001283 0.00058 0.00082 Make BZMFG 1489.3
1001258 1001285 0.00058 0.00082 Make BZMFG 617.91
1001258 1001286 0.00058 0.00082 Make BZMFG 739.92
1001258 1001290 0.00058 0.00082 Make BZMFG 617.91
1001258 1001291 0.00058 0.00082 Make BZMFG 739.92
1001258 1001297 0.00058 0.00082 Make BZMFG 617.91
1001258 1001298 0.00058 0.00082 Make BZMFG 739.92
1001258 1001299 0.00058 0.00082 Make BZMFG 984.47
1001258 1001300 0.00058 0.00082 Make BZMFG 1235.8
1001258 1001301 0.00058 0.00082 Make BZMFG 984.47
1001258 1001302 0.00058 0.00082 Make BZMFG 1235.8
1001258 1001303 0.00058 0.00082 Make BZMFG 984.47
1001258 1001304 0.00058 0.00082 Make BZMFG 1235.8
1001258 1001311 0.00058 0.00082 Make BZMFG 667.63
1001258 1001312 0.00058 0.00082 Make BZMFG 801.16
1001258 1001313 0.00058 0.00082 Make BZMFG 1065.76
1001258 1001314 0.00058 0.00082 Make BZMFG 1335.26
1001258 1001315 0.00058 0.00082 Make BZMFG 1613.44
1001258 1003265 0.00058 0.00082 Make BZMFG 1489.3
1001258 1003273 0.00058 0.00082 Make BZMFG 1489.3
1001258 1003441 0.00058 0.00082 Make BZMFG 1489.3
1001258 1003446 0.00058 0.00082 Make BZMFG 1489.3
1001258 1004574 0.00058 0.00082 Make BZMFG 617.91
1001258 1004575 0.00058 0.00082 Make BZMFG 739.92
1001258 1004576 0.00058 0.00082 Make BZMFG 984.47
1001258 1004577 0.00058 0.00082 Make BZMFG 1235.8
1001258 1004740 0.00058 0.00082 Make BZMFG 1787.2
1001258 1004745 0.00058 0.00082 Make BZMFG 1787.2
1001258 1008842 0.00058 0.00082 Make BZMFG 739.92
1001258 1008843 0.00058 0.00082 Make BZMFG 739.92
1001311 70-1010-BRX0014 4.59195 6.42873 Make BZMFG 1CTE_ExpertsExchange.xlsx
ASKER
Oh, and I had to make a slight change to your query, in order to produce results.
As shown below, I had to change qty.Parent_Assembly_Key to qty.Assembly_Item_Key. This is because SQL was giving me the following error:
Msg 207, Level 16, State 1, Line 24
Invalid column name 'Parent_Assembly_Key'.
John
As shown below, I had to change qty.Parent_Assembly_Key to qty.Assembly_Item_Key. This is because SQL was giving me the following error:
Msg 207, Level 16, State 1, Line 24
Invalid column name 'Parent_Assembly_Key'.
John
SELECT
costs.Assembly_Item_Key
, qty.Assembly_Item_Key
, costs.Labor_Cost
, costs.Overhead_Cost
, qty.Make_Buy
, qty.Item_Location
, qty.Quantity
FROM costs
INNER JOIN qty ON costs.Assembly_Item_Key = qty.Item_Key
Did you change any headingss?
Where is the Parent_item_key column?
If you changed anything in that query then that ourput isnt helpful. Ir you need to provide the EXACT code that you did run.
Where is the Parent_item_key column?
If you changed anything in that query then that ourput isnt helpful. Ir you need to provide the EXACT code that you did run.
Ha. Thanks.
Sorry can you paste in the complete wuery not just the final bit.
ASKER
Sure!
Here it is:
Here it is:
;WITH
costs (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
where Assembly_Item_Key IN ('70-1010-BRX0014', '1001311', '1001258')
),
qty (Make_Buy, Quantity, Item_Location, Assembly_Item_Key, Item_Key) AS (
SELECT
Items.Make_Buy
, Items.Quantity
, Items.Item_Location
, Items.Assembly_Item_Key Parent_Assembly_Key
, Items.Item_Key
FROM BM_View_SL_BOMItems AS Items
LEFT OUTER JOIN costs AS BOMS ON Items.Item_Key = BOMS.Assembly_Item_Key
WHERE Items.Make_Buy = 'Make'
)
SELECT
costs.Assembly_Item_Key
, qty.Assembly_Item_Key
, costs.Labor_Cost
, costs.Overhead_Cost
, qty.Make_Buy
, qty.Item_Location
, qty.Quantity
FROM costs
INNER JOIN qty ON costs.Assembly_Item_Key = qty.Item_Key
Its ok. I understand now. No need for query again. I will consider output in the morning.
ASKER
Thank you, PortletPaul!
John
John
ASKER
Hi PortletPaul:
Actually, I was able to figure out how to modify the CTE to get the results that I need! The revised query is below.
Also, I have placed the results within a second code block below. In addition, I have embedded its spreadsheet in case that makes it easier to look at.
I'll let you know if there is anything else needed, on this. At least, this gives me the path to keep moving on this project! :)
Thank you, for your tips, as usual!
John
GoodResults_111017AM.xlsx
Actually, I was able to figure out how to modify the CTE to get the results that I need! The revised query is below.
Also, I have placed the results within a second code block below. In addition, I have embedded its spreadsheet in case that makes it easier to look at.
I'll let you know if there is anything else needed, on this. At least, this gives me the path to keep moving on this project! :)
Thank you, for your tips, as usual!
John
;WITH LaborAndOverheadCosts (Assembly_Item_Key, Labor_Cost, Overhead_Cost, Assembly_Location) AS
(
SELECT DISTINCT
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 DISTINCT 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')
)
SELECT DISTINCT LaborAndOverheadCosts.Assembly_Item_Key, 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')Assembly_Item_Key Labor_Cost Overhead_Cost Make_Buy Item_Location Quantity Assembly_Item_Key
1001258 0.000580 0.000820 Make BZMFG 667.630000 1001311
1001311 4.591950 6.428730 Make BZMFG 1.000000 70-1010-BRX0014
70-1010-BRX0014 0.412500 0.577500 NULL NULL NULL NULLGoodResults_111017AM.xlsx
Excellent. You might note that the first part of your cte really isn't doing anything useful.
And i would love to see what happens withour DISTINCT in every query.
By the way. I was able to read your results by using my phone. I woke up during the night. Had a quick look. Made some comments etc. This morning looked at notifications on the phone. Voila! Its all here to see sgain. You cannot imagine how much easier it is when everthing is on the one screen and i don't end up with a multitude of redundant download files. So I really appreciate you pasting the results and queries in as code blocks.
Cheers.
And i would love to see what happens withour DISTINCT in every query.
By the way. I was able to read your results by using my phone. I woke up during the night. Had a quick look. Made some comments etc. This morning looked at notifications on the phone. Voila! Its all here to see sgain. You cannot imagine how much easier it is when everthing is on the one screen and i don't end up with a multitude of redundant download files. So I really appreciate you pasting the results and queries in as code blocks.
Cheers.
ASKER
My pleasure!
John
John
and BM_View_SL_BOMItems.Assemb
and BM_View_SL_BOMItems.Assemb
You do not need the first line because the second line overrules it anyway.
and BM_View_SL_BOMItems.Assemb
You do not need the first line because the second line overrules it anyway.
ASKER
Ah! Thanks, PortletPaul!
John
John
BOMs are hierarchies. In TSQL you can use a "recursive CTE" (common table expression) to traverse hierarchies. There are many examples of "recursive CTEs" available across this site and elsewhere. If you want specific assistance in writing one to suit you we would need "sample data" (not a report, not a query across multiple tables, not images) but some reusable data from each table
the simplest format for us to use is SQL inserts, or just an easily parsed text format such as csv or tab delimited and this is best pasted into a comment as a "code block" (see code in the toolbar) e.g.
Open in new window
We also need your "expected result", this might be produced manually via a spreadsheet, but it should be possible for us to make that result from the data you provide.Not a lot of information is needed. Just a few rows from each table is usually enough.
If you are comfortable with building a sample database you could use http://sqlfiddle.com or http://dbfiddle.uk or http://rextester.com and provide the link.