How can I sum all child's values into the same column for the respective parent row

Hello,
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')
      ,  ('3.1.1.1', 4, 'P', 'P-CHICKEN DEFROSTED', 1.0, NULL, 'P-CHICKEN MARINATED', 16.0, '0.178')
      ,  ('3.1.1.1.1', 5, 'R', 'CHICKEN FILET', 5.0, 2.849, 'P-CHICKEN DEFROSTED', 5, 'n/a')
      ,  ('3.1.1.2', 4, 'R', 'OIL SPRAY', 0.002, 6.974, 'P-CHICKEN MARINATED', 16.0, 'n/a')
      ,  ('3.1.1.3', 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.

Why would my message be deleted? did I not meet a requirement? if so, please let me know so I can fix it. I need my post up so someone can hopefully help answer it. Thanks.

hi

i tried to do this ... hope it helps :slight_smile: :slight_smile:
i love any feedback

i had to make some..changes to get what you want EXACTLY right

drop create data ...
drop table RecipeExploded 
 go 

-- 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')
      ,  ('3.1.1.1', 4, 'P', 'P-CHICKEN DEFROSTED', 1.0, NULL, 'P-CHICKEN MARINATED', 16.0, '0.178')
      ,  ('3.1.1.1.1', 5, 'R', 'CHICKEN FILET', 5.0, 2.849, 'P-CHICKEN DEFROSTED', 5, 'n/a')
      ,  ('3.1.1.2', 4, 'R', 'OIL SPRAY', 0.002, 6.974, 'P-CHICKEN MARINATED', 16.0, 'n/a')
      ,  ('3.1.1.3', 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')
	  

select 'data',* from RecipeExploded
go
SQL ....
; WITH cte 
     AS (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 END ), 
     ex.fordebugexpectedresultforptypes, 
     ex.recipelevel, 
     ex.ingrtype, 
     parentname, 
     parentrecipemakesstd 
     FROM   recipeexploded ex) 
SELECT 'my output', 
       ( Sum(costper1parentstd) 
           OVER( 
             partition BY LEFT(ingrseq, 1) 
             ORDER BY (SELECT NULL)) / parentrecipemakesstd ) * ingramountstd AS 
       sumoverFORMULA, 
       * 
FROM   cte 

go