SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Atlanta, GA
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Rolling up values for one last calculation

    Here's the scenario---

    2 tables:

    table1 ( a lookup table ) 1:N
    id
    ingredient
    gross_cost
    ------------------------
    table2 ( one record per item )
    id
    rec_ingredient
    rec_unit ( a number )
    rec_portion ( a unit of measurement )
    rec_code ( unique identifier )



    Now, using various queries I can calculate values for table2 based on table1.

    I need to be able to tally up all the table2 cost values which I derive from using table1 gross_cost value, then use that total as a new value for one final calculation.

    I thought an join would do this but I'm thinking more and more it'll be a temp table.



    Pete


  2. #2
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Atlanta, GA
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To further elaborate:

    The id fields are the primary keys and serve as row identifiers, the matching occurs on the ingredient/rec_ingredient field.

    The ingredient table stores basic data about an ingredient a chef may need in a recipe and is used to build a varying number of select boxes, each populated with the ingredients stored in the ingredient table. A chef chooses from them to build a recipe.

    The query I'm working on is to generate a food cost calculation based on the data input. The thing is that many of the ingredients vary in price from week to week, so calculating the food cost should be a derived value based on one gross_cost held in the ingredient table for one that ingredient. When an ingredient changes, I don't want to have to update each recipe_ingredient_data record where the ingredient occurs, but simply calculate based on the single gross_cost value stored in ingredient table. Thus the core information for any ingredient is stored but once, in the ingredient table.

    When a chef defines or builds a new recipe he enters the name of the recipe he's about to define and how many ingredients the recipe requires into a form. The number of ingredients he provides is used to build a form dynamically. The form offers X number of select boxes populated with the data in the ingredients table. The chef makes his selections, enters the portion amount and portion type, and a brief desrciption. This is stored in recipe_ingredient_data.

    Later, recipe_ingredient_data is queried, matching on ingredient.ingredient = rec_ingredient_data.rec_ingredient to determine a working food cost for the recipe.

    Something like:

    SELECT r.rec_ingredient AS Ingredient, i.gross_cost AS 'Bulk Price',

    round((( r.rec_unit * r.rec_portion ) * gross_cost) * 100) /100 AS 'Ingredient Cost',

    sum(round(((( r.rec_unit * r.rec_portion ) * gross_cost) * 100) /100)) * (r.rec_unit * r.rec_portion) * gross_cost AS '% of total'

    FROM recipe_ingredient_data r

    LEFT JOIN
    ingredient i ON i.ingredient = r.rec_ingredient

    WHERE r.rec_code = 'id3c9cb1714' //variable rec_code

    GROUP BY r.rec_ingredient

    ORDER BY r.rec_ingredient
    ---------------------------------

    Regards,

    Pete


  3. #3
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Atlanta, GA
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Finally ended up with this:

    Never knew we could assign a variable as in first SELECT @total_cost:=....

    That comes in handy....

    Code:
    SELECT @total_cost:=sum(round(r.rec_unit * r.rec_portion * gross_cost * 100) /100) from 
    FROM recipe_ingredient_data AS r 
    LEFT JOIN ingredient AS i 
    ON i.ingredient = r.rec_ingredient 
    WHERE r.rec_code = 'id3c9cb1714';
    
    SELECT r.rec_ingredient AS Ingredient, i.gross_cost AS 'Bulk Price', 
    round(r.rec_unit * r.rec_portion * gross_cost * 100) /100 AS 'Ingredient Cost', 
    round(r.rec_unit * r.rec_portion * gross_cost * 100 / @total_cost ) AS '% of total' 
    FROM recipe_ingredient_data AS r 
    LEFT JOIN ingredient AS i
    ON i.ingredient = r.rec_ingredient 
    WHERE r.rec_code = 'id3c9cb1714' ORDER BY r.rec_ingredient;



Bookmarks

Posting Permissions

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