CODEWITHSUNDEEP
sqlsql-server
HarryD
HarryD

Reputation: 37

Stock balance from items in Boms

I have Bom table like

ItemCode  MatCode  Quantity 
 032490   M10506     3.0 
 032490   M10507     1.0
 032490   M10509     1.0
 030515   M10506     2.0
 030515   M10508     5.0
 045660   M10507     2.0
 045660   M10510     3.0

Stock balance is known for assembled items in Item table.

 ItemCode   StockBalance
  032490        10
  030515        15
  045660         8
  M10506     
  M10507     
  M10508     
  M10509     
  M10510

I need to calculate balance for all materials (MatCode) in Stock as part of assembled products. So in my example for M10506 it would be 10*3+15*2.

I'm stuck with this, looping the query and right now there's only 1 level boms but in future there will be 2 and 3 level boms.

My query:

Select sum(item.StockBalance * bom.Quantity) from item 
 inner join bom on bom.ItemCode=item.itemcode
 Where  exists (select * from bom WHERE Item.itemCode = bom.ItemCode)
 Group by bom.ItemCode

Upvotes: 1

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Your data suggests a join and aggregation:

select b.matCode, sum(b.quantity * i.StockBalance)
from bom b join
     item i
     on b.itemCode = i.itemCode
group by b.matCode;

Upvotes: 2

Related Questions