# 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:

``````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],
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'

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
###### Who is Participating?

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.

Forgive me, but I (and I suspect others) are unlikely to download 8 strange files and take the time to understand each one.  I have grappled with those .rpt files in the past and they are not friendly to use as inputs to a table (mostly because NULLs are just left blank and it becomes a nightmare to parse the data).

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.
``````MyTable
id partno parentpartno
1  100    10
``````
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.
0
Database ExpertCommented:
Can you pls provide input and output in an excel.?
0
Author Commented:
Hi:

Below are all of the corresponding files that I attached, yesterday.  This time, all files are in Excel format--not .rpt format.

Thanks!  Again, much appreciated!

John

Report.xlsx
70-1010-BRX0014-Cost-Breakdown_.xlsx
Quantity_70-1010-BRX0014.xlsx
Quantity_1001311.xlsx
Quantity_1001258.xlsx
0
DeveloperCommented:
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 Recipe
``````
0
Author Commented:
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
0
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.
0
Author Commented:
Hi PortletPaul:

The file showing the hierarchy is the "70-1010-BRX0014-Cost-Breakdown_.xlsx" spreadsheet.  The column names within all of the files are obvious.

If this helps, someone can simply give me additional hints on how to do this.

Thanks, again!

John
0
Author Commented:
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

``````;WITH LaborAndOverheadCosts (Assembly_Item_Key, Labor_Cost, Overhead_Cost, Assembly_Location) AS
(
SELECT DISTINCT
Assembly_Item_Key,
Labor_Cost,
FROM BM_View_SL_BOMS
),

MakeQuantities (Make_Buy, Quantity, Labor_Cost, Item_Key, Item_Location) AS
(
SELECT DISTINCT
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
)

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

70-1010-BRX0014-Cost-Breakdown_.xlsx
CTE.xlsx
0
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?

``````| 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       |
``````
Is the column Item_Location 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)
1
Author Commented:
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
0
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.
0
Author Commented:
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

``````;WITH LaborAndOverheadCosts (Assembly_Item_Key, Labor_Cost, Overhead_Cost, Assembly_Location) AS
(
SELECT DISTINCT
Assembly_Item_Key,
Labor_Cost,
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)
)

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')
``````
0
OK, these 3 lines do appear to form a hierarchy
``````|    | Assembly_Item_Key | Labor_Cost | Overhead_Cost | Make_Buy | Item_Location | Quantity | Assembly_Item_Key |
|----|-------------------|------------|---------------|----------|---------------|----------|-------------------|
|  9 | 1001258           |    0.00058 |       0.00082 | Make     | BZMFG         | 667.63   | 1001311           |
| 45 | 1001311           |    4.59195 |       6.42873 | Make     | BZMFG         | 1        | 70-1010-BRX0014   |
| 46 | 70-1010-BRX0014   |     0.4125 |        0.5775 | NULL     | NULL          | NULL     | NULL              |
``````
What does this query produce please?
``````;WITH
costs (Assembly_Item_Key, Labor_Cost, Overhead_Cost, Assembly_Location) AS (
SELECT
Assembly_Item_Key
, Labor_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.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
)
SELECT
costs.Assembly_Item_Key
, qty.Parent_Assembly_Key
, costs.Labor_Cost
, qty.Item_Location
, qty.Quantity
FROM costs
INNER JOIN qty ON costs.Assembly_Item_Key = qty.Item_Key
``````
Instead of yet another spreadsheet any chance of just pasting the results into a code block?
0

Experts Exchange Solution brought to you by

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

Author Commented:
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

``````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	1
``````

CTE_ExpertsExchange.xlsx
0
Author Commented:
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

``````SELECT
costs.Assembly_Item_Key
, qty.Assembly_Item_Key
, costs.Labor_Cost
, qty.Item_Location
, qty.Quantity
FROM costs
INNER JOIN qty ON costs.Assembly_Item_Key = qty.Item_Key
``````
0
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.
0
Ha. Thanks.
0
Sorry can you paste in the complete wuery not just the final bit.
0
Author Commented:
Sure!

Here it is:

``````;WITH
costs (Assembly_Item_Key, Labor_Cost, Overhead_Cost, Assembly_Location) AS (
SELECT
Assembly_Item_Key
, Labor_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.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
)
SELECT
costs.Assembly_Item_Key
, qty.Assembly_Item_Key
, costs.Labor_Cost
, qty.Item_Location
, qty.Quantity
FROM costs
INNER JOIN qty ON costs.Assembly_Item_Key = qty.Item_Key
``````
0
Its ok. I understand now. No need for query again. I will consider output in the morning.
0
Author Commented:
Thank you, PortletPaul!

John
0
Author Commented:
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

``````;WITH LaborAndOverheadCosts (Assembly_Item_Key, Labor_Cost, Overhead_Cost, Assembly_Location) AS
(
SELECT DISTINCT
Assembly_Item_Key,
Labor_Cost,
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
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')
``````

``````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	NULL
``````

GoodResults_111017AM.xlsx
0
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.
0
Author Commented:
My pleasure!

John
0
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')

You do not need the first line because the second line overrules it anyway.
0
Author Commented:
Ah!  Thanks, PortletPaul!

John
0
###### 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
Programming

From novice to tech pro — start learning today.