t-sql bill of material explosion
I have the following challange presented to me.
A product can have several BOM Components. If I explode the BOM it may look like this:
Parent LVL1 QtyPer LVL2 QtyPer LVL3 QtyPer
A AA 0.2 AAA 0.2 NULL NULL
A AA 0.2 AAB 0.3 NULL NULL
A AA 0.2 AB 0.5 CA 0.5
A AA 0.2 AB 0.5 CB 0.5
A AB 0.3 CA 0.5 NULL NULL
A AB 0.3 CB 0.5 NULL NULL
A AC 0.5 AB 1 NULL NULL
See the image for a schematic view.
The question:
I need to select any item (e.g. AA, AB, CA) and calculate how much of it is used in the parent (A). The result should be:
AA --> (1 * 0.2) = 0.2
AB --> (1 * 0.2 * 0.5) + (1 * 0.3) + (1 * 0.5 * 1) = 0.9
CA --> (1 * 0.2 * 0.5 * 0.5) + (1 * 0.3 * 0.5) = 0.2
The exploded BOM table is appr 500.000 rows.
I've attached a script to create the sample.
A product can have several BOM Components. If I explode the BOM it may look like this:
Parent LVL1 QtyPer LVL2 QtyPer LVL3 QtyPer
A AA 0.2 AAA 0.2 NULL NULL
A AA 0.2 AAB 0.3 NULL NULL
A AA 0.2 AB 0.5 CA 0.5
A AA 0.2 AB 0.5 CB 0.5
A AB 0.3 CA 0.5 NULL NULL
A AB 0.3 CB 0.5 NULL NULL
A AC 0.5 AB 1 NULL NULL
See the image for a schematic view.
The question:
I need to select any item (e.g. AA, AB, CA) and calculate how much of it is used in the parent (A). The result should be:
AA --> (1 * 0.2) = 0.2
AB --> (1 * 0.2 * 0.5) + (1 * 0.3) + (1 * 0.5 * 1) = 0.9
CA --> (1 * 0.2 * 0.5 * 0.5) + (1 * 0.3 * 0.5) = 0.2
The exploded BOM table is appr 500.000 rows.
I've attached a script to create the sample.
CREATE TABLE TestBom (
Parent varchar(1),
LVL1 varchar(2),
QtyPer1 float,
LVL2 varchar(3),
QtyPer2 float,
LVL3 varchar(3),
QtyPer3 float)
INSERT TestBom (Parent, LVL1, QtyPer1, LVL2, QtyPer2, LVL3, QtyPer3)
VALUES ('A','AA','0.2','AAA','0.2',NULL,NULL),
('A','AA','0.2','AAB','0.3',NULL,NULL),
('A','AA','0.2','AB','0.5','CA','0.5'),
('A','AA','0.2','AB','0.5','CB','0.5'),
('A','AB','0.3','CA','0.5',NULL,NULL),
('A','AB','0.3','CB','0.5',NULL,NULL),
('A','AC','0.5','AB','1',NULL,NULL)
Lowfatspread
and the bom table structure is?
ASKER
Two tables:
ITEM TABLE
----------
Item
A
B
C
D
BOM COMPONENT TABLE
-------------------
Partent Item BOM Component QTY PER
A AA 0.2
A AB 0.3
AB CA 0.5
AB CB 0.5
AA AAA 0.2
ITEM TABLE
----------
Item
A
B
C
D
BOM COMPONENT TABLE
-------------------
Partent Item BOM Component QTY PER
A AA 0.2
A AB 0.3
AB CA 0.5
AB CB 0.5
AA AAA 0.2
TRY
Select item
,sum(amt)
FROM (
select
distinct case lvl
when 1 then lvl1
when 2 then lvl1+coalesce(lvl2,'')
else lvl1+coalesce(lvl2,'')+coa
end as lab
,lvl
,case lvl
when 1 then lvl1
when 2 then lvl2
else lvl3
end as item
,case lvl
when 1 then qtyper1
when 2 then qtyper1 * qtyper2
else qtyper1 * qtyper2 * qtyper3
end as amt
from testbom
,(select 1 as lvl union select 2 union select 3) as x
where parent='A'
) AS Z
GROUP BY ITEM
ORDER BY 1
Select item
,sum(amt)
FROM (
select
distinct case lvl
when 1 then lvl1
when 2 then lvl1+coalesce(lvl2,'')
else lvl1+coalesce(lvl2,'')+coa
end as lab
,lvl
,case lvl
when 1 then lvl1
when 2 then lvl2
else lvl3
end as item
,case lvl
when 1 then qtyper1
when 2 then qtyper1 * qtyper2
else qtyper1 * qtyper2 * qtyper3
end as amt
from testbom
,(select 1 as lvl union select 2 union select 3) as x
where parent='A'
) AS Z
GROUP BY ITEM
ORDER BY 1
ASKER
Althoug i can use that query for something else, my user wants to know in what items a BOM Component is used and how much per item. This issue is not what the BOM Component list of an Parent Item is.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Altough I found my own slightly different solution, this is what I am looking for. Thanks!!