Forum Discussion
Provide me with all main products (which are not present in the sub products) with their lowest
I hope you are doing well, please find the problem statement below along with the sample data and output. I have data with a product, sub-product, level and multiplying factor. Each product has ...
Product Sub_product Level Multiplying_Factor
| A | B | Sub1 | 0.9 |
| A | C | Ground | 0.8 |
| A | D | Ground | 0.7 |
| B | F | Ground | 0.3 |
| G | H | Sub1 | 0.87 |
| H | I | Sub2 | 0.99 |
| I | J | Ground | 0.88 |
| G | B | Sub1 | 0.89 |
| B | F | Ground | 0.3 |
Product Sub_product level Multiplying_Factor
| A | C | Ground | 0.8 |
| A | D | Ground | 0.7 |
| A | F | Ground | 0.27 |
| G | F | Ground | 0.267 |
| G | J | Ground | 0.757944 |
SQL:
select * from Bom_Calc_Factor;
with recursive under_alice as (select Product top,level,Sub_product,Product,Multiplying_Factor,Multiplying_Factor path from Bom_Calc_Factor where Product in (select distinct Product from Bom_Calc_Factor where Level like 'Sub1') union
select top,x.level,x.Sub_product,x.Product,x.Multiplying_Factor,under_alice.path*x.Multiplying_Factor from Bom_Calc_Factor x join under_alice on x.Product=under_alice.Sub_product)
select top Product,Sub_product,Level,path Multiplying_Factor from under_alice where Level like 'GROUND';