Linking to the next recommended Product based on stock availability-CodePudding

The

Then here's the second table which contains the trial scheme and outlined the best chemical fit, second-best, and third-best alternative for each sample:

And finally, here's the desired output which will recommend product application based on availability of stock in each bottle.

The record highlighted in red is just to show where the recommendation needs to switch to second-best alternative because we ran out of stock in Product A.

Any help or input would be appreciated. Thanks in advance.

CodePudding user response：

Your problem is that your second table is not normalized, which makes writing queries against it difficult. Any time you have columns named 1/2/3, that's a strong indication you're not normalizing, limiting what the database can do and making it difficult for yourself.

Start by normalizing it, and store that in a separate query. Most RDBMSes have an UNPIVOT query to normalize, but Access only has UNION to do so.

qryTable2Normalized

SELECT Sample, BestChem1 As Chem, 1 As Position FROM Table2 UNION ALL SELECT Sample, BestChem2 As Chem, 2 As Position FROM Table2 UNION ALL SELECT Sample, BestChem3 As Chem, 3 As Position FROM Table2

Really, this should be a third table, though, since if we store it in a separate table we can easily have a fourth best, sixth best, 100th best, etc.

Then, you can select the chemical with the lowest position using an ordered subquery.

SELECT Sample, ( SELECT TOP 1 Location FROM Table1 t1 INNER JOIN qryTable2Normalized t22 On t1.Chemical = t22.Chem WHERE t22.Sample = t2.Sample AND t1.Stock <> 0 ORDER BY t22.Position ASCENDING ) As Recommended FROM Table2 t2

CodePudding user response：

I tried to construct some logic for your query.

I would break this down into different parts. First I would take in the consideration of the max stock that is available for each product.

Select chemical, sum(stock) from chemical_table group by chemical;

Next I would assign a number to each product available to help us with recommendation.

with product_order as (Select chemical, bottlecode, stock , 1 as num from chemical_table union all select chemical, bottlecode,stock, num 1 from product_number where num < stock) select * from product_number;

Next, I would look at sample_table to do running totals for each chemical product for all the three options.

Select sample ,bestChem1 ,count(bestChem1) over (partition by bestChem1 order by sample between current row and unbounded preceding ) as requirement_bestChem1 ,bestChem2 ,count(bestChem2) over (partition by bestChem2 order by sample between current row and unbounded preceding ) as requirement_bestChem2 ,bestChem3 ,count(bestChem3) over (partition by bestChem3 order by sample between current row and unbounded preceding ) as requirement_bestChem3 from sample_table;

Now, I can compare max available stock against each sample and add case statements to go for the recommendation using the above results in cte.

--single row max product availability with cte_availability_chemical as (select * from ( select chemical, stock from chemical_table )t pivot( sum(stock) for chemical in ('ProductA', 'ProductB', 'ProductC') ) as pivot_table) , -- there are 5 stocks of product A, this cte produces 5 records for the product for the same bottlecode which has more than 1 stock with product_order as (Select chemical, bottlecode, stock , 1 as num from chemical_table union all select chemical, bottlecode,stock, num 1 from product_number where num < stock) select chemical || num as product_number, bottlecode from product_order, -- provides a count for each time a product has been used a bestChem cte_sample_requirement as (Select sample ,bestChem1 ,count(bestChem1) over (partition by bestChem1 order by sample between current row and unbounded preceding ) as requirement_bestChem1 ,bestChem2 ,count(bestChem2) over (partition by bestChem2 order by sample between current row and unbounded preceding ) as requirement_bestChem2 ,bestChem3 ,count(bestChem3) over (partition by bestChem3 order by sample between current row and unbounded preceding ) as requirement_bestChem3 from sample_table), --simple cross join maintaining the same number of rows as previous cte cte_recommedation_calc1 as (Select csr.*, cac,* from cte_sample_requirement csr cross join cte_availability_chemical cac), --assign a product number to the qualifying available product number cte_recommendation_calc2 as (select sample, case --check for bestChem1 availability when bestChem1 == 'ProductA' and requirement_bestChem1 <= ProductA then 'ProductA' || requirement_bestChem1 when bestChem1 == 'ProductB' and requirement_bestChem1 <= ProductB then 'ProductB' || requirement_bestChem1 when bestChem1 == 'ProductC' and requirement_bestChem1 <= ProductC then 'ProductC' || requirement_bestChem1 --check for bestChem2 availability when bestChem2 == 'ProductA' and requirement_bestChem2 <= ProductA then 'ProductA' || requirement_bestChem2 when bestChem2 == 'ProductB' and requirement_bestChem2 <= ProductB then 'ProductB' || requirement_bestChem2 when bestChem2 == 'ProductC' and requirement_bestChem2 <= ProductC then 'ProductC' || requirement_bestChem2 --check for bestChem3 availability when bestChem3 == 'ProductA' and requirement_bestChem3 <= ProductA then 'ProductA' || requirement_bestChem3 when bestChem3 == 'ProductB' and requirement_bestChem3 <= ProductB then 'ProductB' || requirement_bestChem3 when bestChem3 == 'ProductC' and requirement_bestChem3 <= ProductC then 'ProductC' || requirement_bestChem3 end as product_number from cte_recommendation_calc1), cte_recommendation as ( select rec.sample, ord.bottlecode from cte_recommendation_calc2 rec left outer join product_order ord on rec.product_number = ord.product_number ) select * from cte_recommendation;

There can be further optimisations steps and deductions to the above.