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

Question

Post reply

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

pietlinden

SSC Guru

Points: 63578
More actions
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!

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

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

Site Owners SSC Guru Points: 80342 More actions · Answer 1

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

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

Jeff Moden SSC Guru Points: 1004595 More actions Follow · Answer 2

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)

pietlinden SSC Guru Points: 63578 More actions · Answer 3

pietlinden

SSC Guru

Points: 63578

April 20, 2024 at 4:52 pm

#4413563

(Yeah, my browser went a bit nuts)

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

Cookies on SQLServerCentral