Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: Substracting an item list from stock

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 05-21-10 at 22:38. Reason: Add a requirement(in quote).

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 05-21-10 at 22:39. Reason: Add a requirement(in quote).

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  5. #5
    Join Date
    Feb 2004
    Posts
    5

    Smile Thanks!

    This is great Tonkuma, thank you very much!

    Best regards,
    Santiago

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •