How to Calculate Costed Bill Of Material
-
I have a multilevel Bill of Material (BOM). I am trying to write a recursive CTE to calculate the cost of the parent based on the components. I have created a CTE which returns the costs of every item in the BOM. I want to only include the lowest level items and not any parent items in the cost.
I have constructed two tables, BOM and ItemCost. At the end of my post I have posted the code to create the tables and populate them.
Here is my CTE
with MstrTable as (Select Bom.Compitemno,BOM.qtyperparent,ItemCost.Cost,BOM.qtyperparent*ItemCost.Cost as QtyCost from BOM
inner join ItemCost
On Bom.Compitemno = ItemCost.ItemNo
where BOM.itemno = 'Parent1'
union all
Select B.Compitemno,B.qtyperparent,ItemCost.cost,MstrTable.qtyperparent * B.qtyperparent * ItemCost.cost as QtyCost from BOM B
inner join MstrTable on mstrtable.compitemno = B.ItemNo
Inner join ItemCost
on B.CompItemNo = ItemCost.ItemNo
)
select * from mstrtable
This returns the result set:
CompitemnoqtyperparentCostQtyCost
Parent2 4 190 760
Item1 1 10 10
Item2 2 20 40
Item2 5 20 400 <Component cost of Parent2
Item3 3 30 360 <Component cost of Parent2
Sorry about the formatting...
As you can see the problem is Parent2 appears in the list. The components for Parent2, Item2 and Item3 also appear in the list. If I were to do a Sum, the value of Parent2 would be captured twice.
How do I prevent Parent2 from being counted twice?
The maximum number of levels in my BOM can be 11.
Create and populate sample tables.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ItemCost](
[ItemNo] [varchar](15) NULL,
[Cost] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BOM](
[ItemNo] [varchar](15) NULL,
[CompItemNo] [varchar](15) NULL,
[QtyPerParent] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Insert into BOM(ItemNo,CompItemNo,QtyPerParent) values ('Parent1','Item1',1)
Insert into BOM(ItemNo,CompItemNo,QtyPerParent) values ('Parent1','Parent2',4)
Insert into BOM(ItemNo,CompItemNo,QtyPerParent) values ('Parent1','Item2',2)
Insert into BOM(ItemNo,CompItemNo,QtyPerParent) values ('Parent2','Item3',3)
Insert into BOM(ItemNo,CompItemNo,QtyPerParent) values ('Parent2','Item2',5)
Insert into ItemCost (ItemNo,Cost) values('Parent1',790)
Insert into ItemCost (ItemNo,Cost) values('Parent2',190)
Insert into ItemCost (ItemNo,Cost) values('Item1',10)
Insert into ItemCost (ItemNo,Cost) values('Item2',20)
Insert into ItemCost (ItemNo,Cost) values('Item3',30)
-
The following query gives the result I want. But I consider it a ham fisted approach. I am sure there must be a better way and I would appreciate it someone would post a better approach.
with MstrTable as (
Select Bom.Compitemno,BOM.qtyperparent,ItemCost.Cost,BOM.qtyperparent*ItemCost.Cost as QtyCost,Bom.compItemNo as ParentItemNo from BOM
inner join ItemCost
On Bom.Compitemno = ItemCost.ItemNo
where BOM.itemno = 'Parent1'
union all
Select B.Compitemno,B.qtyperparent,ItemCost.cost,MstrTable.qtyperparent * B.qtyperparent * ItemCost.cost as QtyCost, MstrTable.compitemno as ParentItemNo from BOM B
inner join MstrTable on mstrtable.compitemno = B.ItemNo
Inner join ItemCost
on B.CompItemNo = ItemCost.ItemNo
)
select SUM(qtyCost) from (
select
SUM(QtyCost) as QtyCost from MstrTable
where ParentItemNo <> compitemno
union all
Select SUM(QtyCost) as QtyCost from MstrTable
where ParentItemNo not in (
select
distinct parentItemNo from MstrTable
where ParentItemNo <> compitemno
)) a
-
Further testing on more complicated BOM's show that the above response does not return the correct results.
Creating a BOM listing or an indented BOM is well documented on this and other web sites. But there is a peculiarity of the Costed BOM that does not fit these other examples.
If we just summed the Total Cost for Parent 1 in the above image, the cost for Parent 2 would be captured twice. It would be captured by the third row, $ 760 and then a second time when the component items of parent 2 are added together, row 4 & 5 ($360+$400).
The above solution does not eliminate this duplication. The query needs to eliminate the cost of the any Parent items in the BOM explosion. This will leave only the component costs.
One way to accomplish this is by returning a unique list of Parent Item numbers from the BOM table and Excluding them from the results query.
with MstrTable (CompITemNo, QtyPerParent,ItemCost,QtyCost,ParentItemNo) as
(
Select Bom.Compitemno,BOM.qtyperparent,ItemCost.Cost,BOM.qtyperparent*ItemCost.Cost as QtyCost,Bom.ItemNo as ParentItemNo from BOM
inner join ItemCost
On Bom.Compitemno = ItemCost.ItemNo
where BOM.itemno = 'Parent1'
union all
Select B.Compitemno,B.qtyperparent,ItemCost.cost,cte.qtyperparent * B.qtyperparent * ItemCost.cost as QtyCost, cte.compitemno as ParentItemNo
from BOM B
inner join MstrTable cte
on cte.compitemno = B.ItemNo
Inner join ItemCost
on B.CompItemNo = ItemCost.ItemNo
)
Select
sum(QtyCost) as TotalCostOfBOM
from MstrTable
where CompITemNo not in (
Select Distinct ItemNo from BOM
)
There may be a way to eliminate these Parent rows directly from the CTE results. If anyone know of a way, please post it here.
I couldn't find a place for search tags, so I added the line below.
Costed Bill Of Material, Costed BOM, Bill Of Material Costing, BOM Costing
-
mpdillon (11/16/2014)
Further testing on more complicated BOM's show that the above response does not return the correct results.Creating a BOM listing or an indented BOM is well documented on this and other web sites. But there is a peculiarity of the Costed BOM that does not fit these other examples.
If we just summed the Total Cost for Parent 1 in the above image, the cost for Parent 2 would be captured twice. It would be captured by the third row, $ 760 and then a second time when the component items of parent 2 are added together, row 4 & 5 ($360+$400).
The above solution does not eliminate this duplication. The query needs to eliminate the cost of the any Parent items in the BOM explosion. This will leave only the component costs.
One way to accomplish this is by returning a unique list of Parent Item numbers from the BOM table and Excluding them from the results query.
with MstrTable (CompITemNo, QtyPerParent,ItemCost,QtyCost,ParentItemNo) as
(
Select Bom.Compitemno,BOM.qtyperparent,ItemCost.Cost,BOM.qtyperparent*ItemCost.Cost as QtyCost,Bom.ItemNo as ParentItemNo from BOM
inner join ItemCost
On Bom.Compitemno = ItemCost.ItemNo
where BOM.itemno = 'Parent1'
union all
Select B.Compitemno,B.qtyperparent,ItemCost.cost,cte.qtyperparent * B.qtyperparent * ItemCost.cost as QtyCost, cte.compitemno as ParentItemNo
from BOM B
inner join MstrTable cte
on cte.compitemno = B.ItemNo
Inner join ItemCost
on B.CompItemNo = ItemCost.ItemNo
)
Select
sum(QtyCost) as TotalCostOfBOM
from MstrTable
where CompITemNo not in (
Select Distinct ItemNo from BOM
)
There may be a way to eliminate these Parent rows directly from the CTE results. If anyone know of a way, please post it here.
I couldn't find a place for search tags, so I added the line below.
Costed Bill Of Material, Costed BOM, Bill Of Material Costing, BOM Costing
I'm confused on the costs you display, particularly for the subparts for the Parent 2 item. I can see where the 360 for Item3, but I don't see how you can get 400 for Item 4.
-
Assuming the item name can indicate a leaf level part... might this be what you are looking for
with MstrTable as (
SelectBom.Compitemno
, 1 as [level]
fromBOM
whereBOM.CompItemno = 'Parent1'
UNION ALL
SelectB.Compitemno
, MstrTable.[LEVEL]+1
fromBOM B
inner
joinMstrTable on mstrtable.compitemno = B.ItemNo
)
selectm.*,
c.Cost,
b.QtyPerParent as Qty,
b.QtyPerParent *c.Cost as TotalSum
frommstrtableas m
inner join itemCost as con c.itemNo = m.CompItemNo
inner join bomas bon b.compItemNo= m.CompItemNo
WHEREm.compItemNo like 'item%'
----------------------------------------------------
-
To calculate total cost of conponents for the item (not including cost of the item itself) try
- test data
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ItemCost](
[ItemNo] [varchar](15) NULL,
[Cost] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BOM](
[ItemNo] [varchar](15) NULL,
[CompItemNo] [varchar](15) NULL,
[QtyPerParent] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Insert into BOM(ItemNo,CompItemNo,QtyPerParent) values ('Parent1','Item1',1)
,('Parent1','Parent2',4)
,('Parent1','Item2',2)
,('Parent2','Item3',3)
,('Item2','Item3',7)
,('Parent2','Item2',5)
Insert into ItemCost (ItemNo,Cost) values('Parent1',790)
,('Parent2',190)
,('Item1',10)
,('Item2',20)
,('Item3',30)
Select B.ItemNo,B.Compitemno,B.qtyperparent,i.cost,
B.qtyperparent * i.cost as QtyCost
from BOM B
Inner join ItemCost i
on B.CompItemNo = i.ItemNo
- query
with MstrTable as (Select b.Compitemno
,b.qtyperparent as qtyperMaster
,b.qtyperparent*ItemCost.Cost as CompsCost
,b.Itemno as ParentItemNo -- just to mark last part of the path
,b.itemno as TopItem
from BOM b
inner join ItemCost on b.Compitemno = ItemCost.ItemNo
union all
Select B.Compitemno, MstrTable.qtyperMaster*B.qtyperparent qtyperMaster
, MstrTable.qtyperMaster * B.qtyperparent * ItemCost.cost as CompsCost
, MstrTable.compitemno as ParentItemNo
, MstrTable.TopItem
from BOM B
inner join MstrTable on mstrtable.compitemno = B.ItemNo
Inner join ItemCost on B.CompItemNo = ItemCost.ItemNo
)
select TopItem as Item, sum(CompsCost) as TotalComponentCost
from MstrTable
group by TopItem
-
Lynn,
You are correct. My posted data is incorrect. Please see below.
MMartin1 and Serg-52, Thank you and I will review your comments more closely this evening.
thanks,
pat
-
mpdillon (11/18/2014)
Lynn,You are correct. My posted data is incorrect. Please see below.
MMartin1 and Serg-52, Thank you and I will review your comments more closely this evening.
thanks,
pat
Still looks the same as that posted earlier. Still no help on how these values are being calculated. What, we need to guess at the calculations?
-
Lynn,
There is still an error. Notice line 2 and Line 5 are both Item 2. Yet the grid shows a different per item cost $20 and $40, respectively. Line 5 individual cost should show $ 20. With that:
-
mpdillon (11/18/2014)
Lynn,There is still an error. Notice line 2 and Line 5 are both Item 2. Yet the grid shows a different per item cost $20 and $40, respectively. Line 5 individual cost should show $ 20. With that:
Now that helps.
-
CELKO (11/18/2014)
Google around for the nested sets model for hierarchies. It is a lot faster and easier than your adjacency list model.Here are two article right here on ssc:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply