ParentPart ComponentPart QuantityPer
---------- ------------- -----------
Body Frame 1
Body Hood/Trunk 2
Body Roof 1
Car Body 1
Frame Door 4
Car Engine 1
Door Window 2
Window Glass 100
Roof Window 1
ParentPart - varchar representing an item or entity that is constructed from other items.
create table bom(
ParentPart varchar(100),
ComponentPart varchar(100),
QuantityPer int
);
[/step]
insert into bom(
ParentPart,
ComponentPart,
QuantityPer
) select 'Car', 'Body', 1
union select 'Car', 'Engine', 1
union select 'Frame', 'Door', 4
union select 'Body', 'Frame', 1
union select 'Body', 'Hood/Trunk', 2
union select 'Body', 'Roof', 1
union select 'Door', 'Window', 2
union select 'Window', 'Glass', 100
union select 'Roof', 'Window', 1
;
[/step]
FinishedGood ParentPart ComponentPart QuantityPer ExtendedQuantityPer
------------ ---------- ------------- ----------- -------------------
Car Car Body 1 1
Car Car Engine 1 1
Car Body Frame 1 1
Car Body Hood/Trunk 2 2
Car Body Roof 1 1
Car Frame Door 4 4
Car Door Window 2 8
Car Roof Window 1 1
Car Window Glass 100 900
create function [dbo].[fn_GetMultiLevelBOM]
(
-- Input parameters
@FinishedGood varchar(100)
)
returns
@BOM table
(
-- Returned table layout
FinishedGood varchar(100),
ParentPart varchar(100),
ComponentPart varchar(100),
QuantityPer int,
ExtendedQuantityPer int
)
as
begin
-- Function body below goes here
return
end
Note here that "finished good" is used to identify to parent part passed, but this could be a sub-assembly (i.e., a part that is a parent but also a component of a higher-level part).
-- add current level
insert into @BOM
select ParentPart, ParentPart, ComponentPart, QuantityPer, QuantityPer
from BOM
where ParentPart = @FinishedGood
;
-- explode downward
insert into @BOM
select c.FinishedGood, n.ParentPart, n.ComponentPart, n.QuantityPer
, n.ExtendedQuantityPer * c.QuantityPer
from @BOM c
cross apply dbo.fn_GetMultiLevelBOM(c.ComponentPart) n
;
This will result in:
FinishedGood ParentPart ComponentPart QuantityPer ExtendedQuantityPer
------------ ---------- ------------- ----------- -------------------
Car Car Body 1 1
Car Car Engine 1 1
Car Body Frame 1 1
Car Body Hood/Trunk 2 2
Car Body Roof 1 1
Car Frame Door 4 4
Car Door Window 2 8
Car Roof Window 1 1
Car Window Glass 100 800
Car Window Glass 100 100
Note that Window - Glass combination appears twice since it is on the BOM of two different components Roof and Door.
select FinishedGood, ParentPart, ComponentPart
, QuantityPer, sum(ExtendedQuantityPer) as ExtendedQuantityPer
from dbo.fn_GetMultiLevelBOM('Car')
group by FinishedGood, ParentPart, ComponentPart, QuantityPer
;
select ParentPart as FinishedGood, ParentPart, ComponentPart
, QuantityPer, QuantityPer as ExtendedQuantityPer
from BOM
where ParentPart = @FinishedGood
(or as shown in the question)
select ParentPart as FinishedGood, ParentPart, ComponentPart
, QuantityPer, QuantityPer as ExtendedQuantityPer
from BOM
where not exists (
select 1
from BOM as lookup
where lookup.ComponentPart = BOM.ParentPart
)
Both of these in our simple example, gets us the following:
FinishedGood ParentPart ComponentPart QuantityPer ExtendedQuantityPer
------------ ---------- ------------- ----------- -------------------
Car Car Body 1 1
Car Car Engine 1 1
select c.FinishedGood, n.ParentPart, n.ComponentPart
, n.QuantityPer, n.QuantityPer * c.ExtendedQuantityPer
from BOM n
inner join {previous query} c on c.ComponentPart = n.ParentPart
;with mlBOM
as
(
select ParentPart as FinishedGood, ParentPart, ComponentPart
, QuantityPer, QuantityPer as ExtendedQuantityPer
from BOM
where ParentPart = 'Car'
union all -- CTE recursion
select c.FinishedGood, n.ParentPart, n.ComponentPart
, n.QuantityPer, n.QuantityPer * c.ExtendedQuantityPer
from BOM n
inner join mlBOM c on c.ComponentPart = n.ParentPart
)
-- final select aggregating ExtendedQuantityPer values
-- displays unique ParentPart - ComponentPart combinations
select FinishedGood, ParentPart, ComponentPart, QuantityPer
, sum(ExtendedQuantityPer) as ExtendedQtyPer
from mlBOM
group by FinishedGood, ParentPart, ComponentPart, QuantityPer
;
create table #mlBOM(
BomLevel int,
FinishedGood varchar(100),
ParentPart varchar(100),
ComponentPart varchar(100),
QuantityPer int,
ExtendedQuantityPer int
);
-- counter variable
declare @CurrentLevel int;
set @CurrentLevel = 0;
-- add current level
insert into #mlBOM
select @CurrentLevel, ParentPart, ParentPart, ComponentPart
, QuantityPer, QuantityPer
from BOM
where ParentPart = 'Car'
;
-- iteratively add secondary levels
while exists(
select 1
from BOM n
inner join #mlBOM c
on c.ComponentPart = n.ParentPart
and c.BomLevel = @CurrentLevel
)
begin
insert into #mlBOM
select c.BomLevel+1, c.FinishedGood, n.ParentPart, n.ComponentPart
, n.QuantityPer, n.QuantityPer * c.ExtendedQuantityPer
from BOM n
inner join #mlBOM c on c.ComponentPart = n.ParentPart
and c.BomLevel = @CurrentLevel
;
-- increment counter
set @CurrentLevel = @CurrentLevel + 1;
end
select FinishedGood, ParentPart, ComponentPart, QuantityPer
, sum(ExtendedQuantityPer) as ExtendedQtyPer
from #mlBOM
group by FinishedGood, ParentPart, ComponentPart, QuantityPer
;
drop table #mlBOM;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (4)
Commented:
Author
Commented:Your article was definitely instrumental in how this one shaped up and gives readers a good understanding IMHO of real-world BOM and other hierarchical data structures this technique could apply to.
Glad you liked this and thanks again.
Regards,
Kevin
Author
Commented:--Kevin
Commented:
Open in new window