CODEWITHSUNDEEP
sql
user3335691
user3335691

Reputation: 31

BOM Query with Hierarchyid

I have try 2 method for query the BOM first using materialized path and hierarchyid. But the result is still incorrect. The result for ItemNumber /1/5/ with qty 3 is 38.12, This result must 114.36 because multiply by qty. Could some one explain me how to solve this problem.

Here's the code :

declare @BOMStructure as table  
(
PartNumber varchar(14)not null ,
Descript varchar(50)not null,
Qty integer not null default 0,
Price Decimal (10,2) default 0,
ItemNumber hierarchyid not null primary key 
)

INSERT @BOMStructure 
(PartNumber ,Descript ,Qty ,Price ,ItemNumber)
VALUES  ('13400201001','MAIN ASSEMBLY',1,0,'/'),
        ('00150060060005','BASIC TANK',1,0,'/1/'),
        ('11012142200503','SHELL',1,789.89,'/1/1/'),
        ('12052140503','TOP CONE',1,226.75,'/1/2/'),
        ('13052140503','BOTTOM CONE',1,226.75,'/1/3/'),
        ('140104116508','PIPE LEG',3,39.75,'/1/4/'),
        ('15004104','BALL FEET',3,0,'/1/5/'),
        ('1510413504','SLEEVE',1,18.03,'/1/5/1/'),
        ('1524809510','ADJUSTABLE BOLT',1,12.82,'/1/5/2/'),
        ('1530411604','BASE',1,7.27,'/1/5/3/')

-- GetAncestor
-- Mengupdate 
select  PartNumber, Descript,Qty,Price, (select sum (Price * Qty)
                                        from @BOMStructure where ItemNumber .IsDescendantOf (p.ItemNumber ) = 1 ) as [TotalPrice], 
        ItemNumber .ToString() as [Hierarcy], ItemNumber .GetLevel() as [Level]

from @BOMStructure as P;

Upvotes: 0

Views: 217

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can solve the problem by multiplying by p.qty:

SELECT partnumber, descript, qty, price, 
       p.qty * (SELECT Sum(price * qty) 
                FROM bomstructure 
                WHERE itemnumber.Isdescendantof(p.itemnumber) = 1
               ) as [TotalPrice], 
       itemnumber.Tostring() as [Hierarcy], 
       itemnumber.Getlevel() as [Level] 
FROM bomstructure as P;

However, I'm not 100% sure that this does what you need for all the rows.

Here is a SQL Fiddle.

Upvotes: 1

Related Questions