SQL語法針對 BOM減少一階 後做用料量彙總

–bom2要被減少的階層 此處用量皆為重量，算出總重量，屆時可依比例更新用量回主BOM，即bom1

update Cf_inv_bom_2
set Cf_inv_bom_2.sum_qty = t1.c_sum
from (select assy_no,sum(qty) c_sum from Cf_inv_bom_2 group by assy_no) t1
where Cf_inv_bom_2.assy_no = t1.assy_no

–bom1無下階先抓出來

select * from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )

–再結合有bom1有下階的資料(用量=>被替換子件原用量*(下階子件用量/下階用量之和))

select * from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )
union all
select T1.assy_no,T2.item_no,CAST(t1.qty*t2.qty/t2.sum_qty AS DECIMAL(10,4) ) d_qty,T1.sum_qty
from cf_inv_bom_2 T2,cf_inv_bom_1 T1
where T2.assy_no=T1.item_no order by 1,2

–由於合併後發現兩階間有共用子件因此多一層group來加總資件用量

select Tu.assy_no,tu.item_no,sum(tu.qty),max(Tu.sum_qty) from
(select [assy_no]
,[item_no]
,[qty]
,[sum_qty] from cf_inv_bom_1
where item_no not in (select assy_no from cf_inv_bom_2 )
union all
select T1.assy_no,T2.item_no,CAST(t1.qty*t2.qty/t2.sum_qty AS DECIMAL(10,4) ) d_qty,T1.sum_qty
from cf_inv_bom_2 T2,cf_inv_bom_1 T1
where T2.assy_no=T1.item_no) as Tu
group by Tu.assy_no,tu.item_no
order by 1,2