Aggregate with subselect or subquery

Question

I'm trying to make a query that returns how much I can produce based on the lowest value of a sum of component units from different warehouses.

Data come from tables like these:

e.table
-------
codartp    codartc      unicompo
REF1       BLISTER1       1
REF1       BOX1           1
REF1       CHARGER1       2
REF2       BLISTER2       1
REF2       BOX2           1

s.table
------
codart      unidades      codalm
BLISTER1      100           4 
BLISTER1      150           1
BOX1          100           1
BOX1          100           4
BOX1          200           2
CHARGER1      50            3
CHARGER1      50            4 
BLISTER2      500           1
BLISTER2      300           4 
BOX2          150           2

I would need to sum how much I have from every component (blister, box, charger...) in total: BLISTER1: 250 BOX1: 400 CHARGER1: 100

And then return the lowest value divided by the unicompo (the number of components I need). With my stock of components, I could only produce 50 units of REF1: 100/2

Here is my subselect:

(select min(val) from (select sum(s1.unidades/e.unicompo) as val 
    from __STOCKALMART s1
    left join escandallo e on e.codartc = s1.codart
    where s1.codalm not in (0,9999) and e.codartp = l.codart) t) as PRODUCIBLE

The expected result would be something like this:

l.codart  producible
REF1          50
REF2          150

But I only managed to call either the min (in some warehouses is 0) or the sum of components, not min after the sum. I don't know if I'm explaining myself. Ask if I need to clarify anything

Thanks for your help

Answer 1

As long as there is no overlap between your product compositions (so no two products share a common component, otherwise you are looking at something way more complex), then the query below should give you a solution.

Sample data

create table ProductComponent
(
  codartp nvarchar(4),  -- product
  codartc nvarchar(10), -- component
  unicompo int          -- amount
);

insert into ProductComponent (codartp, codartc, unicompo) values
('REF1', 'BLISTER1', 1),
('REF1', 'BOX1'    , 1),
('REF1', 'CHARGER1', 2),
('REF2', 'BLISTER2', 1),
('REF2', 'BOX2'    , 1);

create table ComponentInventory
(
  codart nvarchar(10), -- component
  unidades int,        -- amount
  codalm int           -- warehouse
);

insert into ComponentInventory (codart, unidades, codalm) values
('BLISTER1', 100, 4), 
('BLISTER1', 150, 1),
('BOX1'    , 100, 1),
('BOX1'    , 100, 4),
('BOX1'    , 200, 2),
('CHARGER1', 50 , 3),
('CHARGER1', 50 , 4), 
('BLISTER2', 500, 1),
('BLISTER2', 300, 4), 
('BOX2'    , 150, 2);

Solution

Using some common table expressions (CTE's) to:

1. Sum up all the inventory components across the warehouses.
2. Divide the previous sums according to the product composition.

This gives:

with cti as -- component inventory total
(
  select ci.codart,
         sum(ci.unidades) as unidades
  from ComponentInventory ci
  group by ci.codart
),
pci as -- product component inventory
(
  select pc.codartp,
         cti.unidades / pc.unicompo as maxPart
  from ProductComponent pc
  join cti
    on cti.codart = pc.codartc
)
select pci.codartp,
       min(pci.maxPart) as producibleAmount
from pci
group by pci.codartp
order by pci.codartp;

Result

codartp producibleAmount
------- ----------------
REF1    50
REF2    150

Fiddle to see it in action with intermediate CTE results.