Post reply

Do I need a recursive query for this? bill of materials (sort of)

  • April 17, 2024 at 11:06 pm

    #4412572

    I am working on a data warehouse project for a bakery. For the most part, the basic structure is like this:

    Purchase--(M,1)--Ingredient--(1,M)--Recipe--(M,1)--Product

    with one exception the "Recipe" part works fine. (IngredientKey, ProductKey, Weight).  The part I'm having trouble with is that one ingredient (starter) consists of other ingredients (kinda like a subassembly, if you wanted to compare to AdventureWorks).

    Starter consists of {a parts Water, b parts Flour, c parts Potato Flakes} - all of which exist in the Ingredients table. How can I model this so that I can do something like SUMX('Recipe',[Weight] * RELATED('Ingredient'[UnitCost]) ) to calculate the total cost of ingredients? Do I need recursion for this? (Seems like overkill). I was thinking I could create a Common Table Expression in SQL Server to join to "Starter" and pass the [final] weight of that down the to "recipe" and just express that as X parts water, Y parts flour, Z parts potato flakes. (X / (X+Y+Z))

    The reason for all this is that then the unit cost for Starter will change properly whenever a component cost changes.

    Any thoughts how to model this? Thanks!

  • April 18, 2024 at 12:16 pm

    This was removed by the editor as SPAM

  • April 18, 2024 at 11:10 pm

    #4412954

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • April 19, 2024 at 3:25 am

    #4412991

    Heh... you, of all people, should know about "double posting".

    Folks, no answers here, please.  Instead, go to the duplicate post and post there.  Thanks.

    https://www.sqlservercentral.com/forums/topic/do-i-need-a-recursive-query-for-this-bill-of-materials-sort-of

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 20, 2024 at 4:52 pm

    #4413563

    (Yeah, my browser went a bit nuts)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply