I'm writing a query (SQL Server 2016) to get the cost of a recipe. The recipe can be a mixture of subrecipes and raw items. A subrecipe can have multiple nested levels and again, its ingredients can be of type raw or subrecipes. (Recipe > Subrecipe > raw items).
Using a recursive CTE, I already exploded the recipe to all it's subrecipes and raw items.
However, I'm having problems to find a way to roll-up raw items costs from the lowest level, to the intermediate subrecipes, and finally, up to the 1st level. Subrecipes never have costs attached. instead, they need to be calculated at the moment based on the cost of its childs.
A subrecipe cost should be the SUM of all its child costs (raw or subrecipes). But before this subrecipe's cost can be rolled upwards, it needs an additional calculation. The formula for subrecipe costs is something like this: ( SUM() / HowMuchThisSubRecipeMakes ) * HowMuchOfThisSubRecipeIsUsed
I've been trying subqueries in join statements, and lately was trying SUM() OVER ... clauses; but I still can't get to the expected results. Please see result's column 'ForDEBUGExpectedResultForPTypes' for the expected rolled-up cost for each subrecipe row
-- TEST SCHEMA: CREATE TABLE RecipeExploded ( IngrSeq NVARCHAR(50) ,RecipeLevel SMALLINT ,IngrType NVARCHAR(1) ,IngrName NVARCHAR(100) ,IngrAmountStd FLOAT ,IngrUnitCostStd FLOAT ,ParentName NVARCHAR(100) ,ParentRecipeMakesStd FLOAT ,ForDEBUGExpectedResultForPTypes NVARCHAR(15) ) INSERT INTO RecipeExploded (IngrSeq, RecipeLevel, IngrType, IngrName, IngrAmountStd, IngrUnitCostStd, ParentName, ParentRecipeMakesStd, ForDEBUGExpectedResultForPTypes) VALUES ('1', 1, 'R', 'RICE', 0.438, 1.134, '(FINAL DISH)', 1, 'n/a') , ('2', 1, 'P', 'P-BLACK BEANS', 0.031, NULL, '(FINAL DISH)', 1, '0.019') , ('2.1', 2, 'R', 'BLACK BEANS CAN', 0.625, 0.770, 'P-BLACK BEANS', 0.875, 'n/a') , ('2.2', 2, 'P', 'P-HOT SALSA', 0.008, NULL, 'P-BLACK BEANS', 0.875, '0.049') , ('2.2.1', 3, 'R', 'TOMATO', 4.0, 1, 'P-HOT SALSA', 2.5, 'n/a') , ('2.2.2', 3, 'R', 'CHILI SAUCE', 8.75, 1.105, 'P-HOT SALSA', 2.5, 'n/a') , ('3', 1, 'P', 'P-CHICKEN PATTY', 3.0, NULL, '(FINAL DISH)', 1, '1.372') , ('3.1', 2, 'P', 'P-CHICKEN COOKED', 3.0, NULL, 'P-CHICKEN PATTY', 24, '0.457') , ('3.1.1', 3, 'P', 'P-CHICKEN MARINATED', 6.0, NULL, 'P-CHICKEN COOKED', 0.375, '3.218') , ('184.108.40.206', 4, 'P', 'P-CHICKEN DEFROSTED', 1.0, NULL, 'P-CHICKEN MARINATED', 16.0, '0.178') , ('220.127.116.11.1', 5, 'R', 'CHICKEN FILET', 5.0, 2.849, 'P-CHICKEN DEFROSTED', 5, 'n/a') , ('18.104.22.168', 4, 'R', 'OIL SPRAY', 0.002, 6.974, 'P-CHICKEN MARINATED', 16.0, 'n/a') , ('22.214.171.124', 4, 'R', 'MARINADE', 0.125, 2.846, 'P-CHICKEN MARINATED', 16.0, 'n/a') , ('3.1.2', 3, 'R', 'OIL SPRAY', 0.024, 6.765, 'P-CHICKEN COOKED', 0.375, 'n/a') -- QUERY SO FAR: SELECT ex.IngrSeq ,IngrNameFormatted = REPLICATE('....', ex.RecipeLevel - 1) + ex.IngrName ,IngrAmountStd ,IngrUnitCostStd ,CostPer1ParentStd = (CASE WHEN ex.IngrType='R' AND ex.RecipeLevel=1 THEN (ex.IngrAmountStd * ex.IngrUnitCostStd) WHEN ex.IngrType='R' AND ex.RecipeLevel>1 THEN (ex.IngrAmountStd * ex.IngrUnitCostStd) / ex.ParentRecipeMakesStd WHEN ex.IngrType='P' THEN NULL /*Here's where I need the help: The formula should be: ( SUM(<the 'CostPer1ParentStd' column of all this prep's childs>) / ParentRecipeMakesStd )* IngrAmountStd */ END) ,ex.ForDEBUGExpectedResultForPTypes ,ex.RecipeLevel ,ex.IngrType ,ParentName ,ParentRecipeMakesStd FROM RecipeExploded ex ORDER BY ex.IngrSeq
Thanks in advance.