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.
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)

Open in new window

BOMExplosion.png
LVL 3
eiramarAsked:
Who is Participating?
 
LowfatspreadCommented:
Select item ,parent
      ,sum(amt)
 FROM (
select
 distinct case lvl
           when 1 then  lvl1
           when 2 then lvl1+coalesce(lvl2,'')
            else lvl1+coalesce(lvl2,'')+coalesce(lvl3,'')
           end as lab
 ,lvl,parent
 ,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
 
 ) AS Z
  GROUP BY parent,ITEM
  ORDER BY 1,2
0
 
LowfatspreadCommented:
and the bom table structure is?
0
 
eiramarAuthor Commented:
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
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
LowfatspreadCommented:
TRY


Select item
      ,sum(amt)
 FROM (
select
 distinct case lvl
           when 1 then  lvl1
           when 2 then lvl1+coalesce(lvl2,'')
            else lvl1+coalesce(lvl2,'')+coalesce(lvl3,'')
           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
0
 
eiramarAuthor Commented:
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.
0
 
eiramarAuthor Commented:
Altough I found my own slightly different solution, this is what I am looking for. Thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.