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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
eiramarAuthor Commented:
Altough I found my own slightly different solution, this is what I am looking for. Thanks!!
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.