Substracting an item list from stock

sbayeta · #1 (**permalink**) 05-21-10, 18:23

Hi,

I'd like to know if is there's a way to achieve the following using SQL.

Suppose I have one table containing recipes like this:

Recipe Ingredient Quantity
rec_A apple 2
rec_A banana 5
rec_A peach 1
rec_B banana 2
rec_B plum 4

And I have another table containing the stock of ingredients:

Ingredient Stock
apple 30
banana 24
peach 32
plum 12

I'd like to perform two different operations:

1. Check the maximum number of portions of one recipe I can make with the stock on hand
1. Substract n times one recipe from the stock

I know I can do this by code in my app (VB.NET application for stock control in PCB's manufacturing), but the 'recipes' (Bill Of Materials) contain thousands of items and I will be substracting every few seconds, so I'm guessing performance will be better if the DB engine does the hard work.

Thanks in advance for any help on this.

Best regards,
Santiago

tonkuma · #2 (**permalink**) 05-21-10, 21:31

Quote:

1. Check the maximum number of portions of one recipe I can make with the stock on hand

One way may be an application of relational division.

Example:

Code:

------------------------------ Commands Entered ------------------------------
WITH
 recipe
( Recipe , Ingredient , Quantity ) AS (
VALUES
  ( 'rec_A' , 'apple'  , 2 )
, ( 'rec_A' , 'banana' , 5 )
, ( 'rec_A' , 'peach'  , 1 )
, ( 'rec_B' , 'banana' , 2 )
, ( 'rec_B' , 'plum'   , 4 )
, ( 'rec_C' , 'banana' , 1 )
, ( 'rec_C' , 'lemon'  , 1 )
)
, stock
( Ingredient , Stock ) AS (
VALUES
  ( 'apple'  , 30 )
, ( 'banana' , 24 )
, ( 'peach'  , 32 )
, ( 'plum'   , 12 )
)
SELECT r.Recipe
     , MIN(s.Stock / r.Quantity) AS max_portions
  FROM recipe r
  LEFT OUTER JOIN
       stock  s
   ON  s.Ingredient = r.Ingredient
 GROUP BY
       r.Recipe
HAVING COUNT(s.Ingredient) = COUNT(*)
;
------------------------------------------------------------------------------

RECIPE MAX_PORTIONS
------ ------------
rec_A             4
rec_B             3

  2 record(s) selected.

tonkuma · #3 (**permalink**) 05-21-10, 21:34

Quote:

1. Check the maximum number of portions of one recipe I can make with the stock on hand

If you want to include all recipe,
remove HAVING clause and modify a little for max_portions.

Example(same data with my previous post):

Code:

SELECT r.Recipe
     , MIN( COALESCE(s.Stock , 0 ) / r.Quantity ) AS max_portions
  FROM recipe r
  LEFT OUTER JOIN
       stock  s
   ON  s.Ingredient = r.Ingredient
 GROUP BY
       r.Recipe
-- HAVING COUNT(s.Ingredient) = COUNT(*)
;
------------------------------------------------------------------------------

RECIPE MAX_PORTIONS
------ ------------
rec_A             4
rec_B             3
rec_C             0

  3 record(s) selected.

tonkuma · #4 (**permalink**) 05-21-10, 23:21

Quote:

1. Substract n times one recipe from the stock

Example:
Subtract two times of recipe 'rec_A' from the stock

Code:

------------------------------ Commands Entered ------------------------------
MERGE INTO
      stock  s
USING (SELECT r.* , p.n
         FROM recipe r
         JOIN (VALUES ('rec_A' , 2) ) p(recipe , n)
          ON  r.recipe = p.recipe
      ) r
  ON  r.Ingredient = s.Ingredient
WHEN MATCHED
THEN UPDATE
     SET stock = stock - r.quantity * r.n
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

Result:

Code:

------------------------------ Commands Entered ------------------------------
SELECT *
  FROM stock
;
------------------------------------------------------------------------------

INGREDIENT STOCK      
---------- -----------
apple               26
banana              14
peach               30
plum                12

  4 record(s) selected.

sbayeta · #5 (**permalink**) 05-22-10, 08:47

This is great Tonkuma, thank you very much!

Best regards,
Santiago

Internet Services

Web Development

Digital Marketing

Consumer Tech