Handling Stock Adjustment in Recursive CTE for Recipes with Shared Ingredients

Question

I am working on a SQL Server 2016 database where I have recipes and ingredients stored in separate tables. The relationship between recipes and ingredients is stored in a junction table. I am using a recursive common table expression (CTE) to calculate missing ingredients for a specific recipe and its child recipes recursively.

However, I have encountered an issue when an ingredient is used in multiple recipes. The stock calculation does not account for the fact that the stock might have already been reserved for other recipes that use the same ingredient. As a result, the stock quantity is not adjusted correctly, leading to inaccurate stock availability.

I am seeking guidance on how to adjust the stock calculation within the recursive CTE to consider the shared usage of ingredients among different recipes. The stock is calculated using an aggregation of ingredient movements from a separate table.

Here is the scenario (SQL FIDDLE):

I have a table named articles with columns code and name, representing the ingredients.

-- auto-generated definition
create table articles
(
    code           int not null
        constraint PK_articles
            primary key,
    name           varchar(254),
)


create index code
    on articles (code)


INSERT INTO articles (code, name) VALUES (2, N'Bottle 2L (Dirty)');
INSERT INTO articles (code, name) VALUES (4, N'Bottle 2L (Washed)');
INSERT INTO articles (code, name) VALUES (6, N'Red Lid');
INSERT INTO articles (code, name) VALUES (9, N'Carbonated Water 2L Etiquette');
INSERT INTO articles (code, name) VALUES (10, N'Carbonated water');

There is a table named recipes with columns code and article, mapping recipes to their corresponding ingredients.

-- auto-generated definition
create table recipes
(
    code   int
        constraint recipes_pk
            primary key,
    article int           not null
)

INSERT INTO recipes (code, article) VALUES (38, 3);
INSERT INTO recipes (code, article) VALUES (39, 4);
INSERT INTO recipes (code, article) VALUES (40, 10);
INSERT INTO recipes (code, article) VALUES (41, 11);

The table articles_in_recipe has columns code, recipe, article, and quantity, indicating the quantity of each ingredient required for a specific recipe.

create table articles_in_recipe
(
    code   int,
    recipe   int            not null,
    article int            not null,
    quantity decimal(15, 3) not null
)
  
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (1, 38, 1, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (137, 41, 8, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (138, 41, 7, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (139, 41, 4, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (145, 40, 9, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (146, 40, 6, 6.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (147, 40, 4, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (149, 39, 2, 6.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (150, 39, 6, 1.000);

The stock_movements table records the movements of ingredients, including the code, article, and quantity columns.


-- auto-generated definition
create table dbo.stock_movements
(
    code             int,
    article           int,
    quantity         DECIMAL(15,3),
)

INSERT INTO stock_movements (code, article, quantity) VALUES (7763977, 6, 5.000);
INSERT INTO stock_movements (code, article, quantity) VALUES (7763978, 7, 5.000);
INSERT INTO stock_movements (code, article, quantity) VALUES (7763979, 2, 5.000);
INSERT INTO stock_movements (code, article, quantity) VALUES (7763981, 4, 29.000);

I'm using the following procedures:

dbo.Calculate_Stock function calculates the stock of an ingredient based on the movements recorded in the stock_movements table.

CREATE FUNCTION [dbo].[Calculate_Stock]
(
    @Code int
)
RETURNS NUMERIC
AS
BEGIN
    DECLARE @return NUMERIC(15,3);

    SELECT @return = ISNULL(SUM(quantity), 0)
    FROM stock_movements
    WHERE article = @Code;

    RETURN @return;
END;

calculate_recipe procedure calculates the required ingredients for a given recipe and checks if they are in stock. It uses a recursive CTE to traverse the recipe hierarchy and adjust quantities based on stock availability.

CREATE PROCEDURE calculate_recipe
(
    @recipe INT,
    @quantity DECIMAL(15, 3)
)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Recursive CTE to calculate required recipes and quantities
    WITH cte AS (
        -- Anchor member retrieves the initial recipe details
        SELECT
            ar.recipe AS recipe,
            ar.article AS article,
            CAST(ar.quantity * @quantity AS DECIMAL(15, 3)) AS total_quantity,
            CASE
                WHEN CAST(ar.quantity * @quantity AS DECIMAL(15, 3)) <= dbo.Calculate_Stock(ar.article) THEN 0
                ELSE CAST(ar.quantity * @quantity - dbo.Calculate_Stock(ar.article) AS DECIMAL(15, 3))
            END AS remaining_quantity,
            1 AS RecursionLevel
        FROM
            recipes r
            INNER JOIN articles_in_recipe ar ON r.code = ar.recipe
        WHERE
            r.code = @recipe
        UNION ALL
        -- Recursive member to calculate required recipes recursively
        SELECT
            ar.recipe AS recipe,
            ar.article AS article,
            CAST(ar.quantity * @quantity AS DECIMAL(15, 3)) AS total_quantity,
            CASE
                WHEN cte.remaining_quantity * ar.quantity <= dbo.Calculate_Stock(ar.article) THEN 0
                ELSE CAST((cte.remaining_quantity * ar.quantity - dbo.Calculate_Stock(ar.article)) AS DECIMAL(15, 3))
            END AS remaining_quantity,
            cte.RecursionLevel + 1
        FROM
            cte
            INNER JOIN recipes r ON cte.article = r.article
            INNER JOIN articles_in_recipe ar ON r.code = ar.recipe
        WHERE
            cte.RecursionLevel < 10 -- Adjust the recursion limit as needed,
            AND cte.remaining_quantity > 0
    )
    -- Insert the required articles and quantities into a temporary table
    SELECT
        recipe,
        article,
        SUM(total_quantity) AS total,
        SUM(remaining_quantity) AS remaining
    INTO #RequiredArticles
    FROM cte
    GROUP BY article, recipe;

    -- Retrieve the final list of required articles and their quantities
    SELECT
        ra.article,
        ra.recipe,
        a.name AS ArticleName,
        ra.total,
        dbo.Calculate_Stock(ra.article) AS stock,
        ra.remaining
    FROM
        #RequiredArticles ra
        INNER JOIN articles a ON ra.article = a.code;

    -- Drop the temporary table
    DROP TABLE #RequiredArticles;

END;

This is a more friendly way of seeing the recipes. If you are wondering why the washed bottle takes 6 dirty bottles and another red lid it was for testing this edge case.

Bottle 2L (Washed)
- Ingredient: Bottle 2L (Dirty)
  - Quantity: 6.000
- Ingredient: Red Lid
  - Quantity: 1.000
Carbonated Water 2L
- Ingredient: Carbonated Water 2L Etiquette
  - Quantity: 1.000
- Ingredient: Red Lid
  - Quantity: 6.000
- Ingredient: Bottle 2L (Washed)
  - Quantity: 1.000

Chatgpt ascii art displaying the tree:

- Carbonated Water 2L
    |- Ingredient: Carbonated Water 2L Etiquette
    |   |- Quantity: 1.000
    |- Ingredient: Red Lid
        |- Quantity: 6.000
    |- Ingredient: Bottle 2L (Washed)
        |- Quantity: 1.000
    |- Bottle 2L (Washed)
        |- Ingredient: Bottle 2L (Dirty)
            |- Quantity: 6.000
        |- Ingredient: Red Lid
            |- Quantity: 1.000

And this is the current stock

Bottle 2L (Dirty)
- Stock: 5
Bottle 2L (Washed)
- Stock: 29
Red Lid
- Stock: 5
Carbonated Water 2L Etiquette
- Stock: 0
Carbonated water
- Stock: 0

When calculating the recipe for 30 carbonated waters, i should fabricate 1 more washed bottle. That bottle will need 1 red lid, but as it's using calcular_stock of current not allocated stock, the procedure believes i have 5 available lids instead of 0 which is incorrect

EXEC calculate_recipe @recipe = 40, @quantity = 30

i get the output. Here you can appreciate remaining 0 on the 6, 39 red lid. Should be 1.

article	recipe	ArticleName	total	stock	remaining
2	39	Bottle 2L (Dirty)	180.000	5	1.000
6	39	Red Lid	30.000	5	0.000
4	40	Bottle 2L (Washed)	30.000	29	1.000
6	40	Red Lid	180.000	5	175.000
9	40	Carbonated Water 2L Etiquette	30.000	0	30.000

Any help or suggestions on modifying the recursive CTE or providing a workaround to accurately adjust the stock quantities would be greatly appreciated.

I have tried a few approaches to resolve this issue. Firstly, I attempted using cursors to iterate through the recipes and adjust the stock accordingly, but it didn't provide the desired outcome and resulted in performance issues. Additionally, I tried incorporating subqueries within the CTE to handle self-referencing, but encountered errors due to the nature of the recursive CTE.

I expected that by modifying the recursive CTE or incorporating subqueries, I would be able to accurately adjust the stock calculation, considering the reservations made for ingredients used in multiple recipes. However, these attempts have not been successful so far.

What result do you want? Do you want to know only the total necessary for each article (summed up across all recursive recipes) or do you need it broken out by child recipe (much more complicated, also need to decide which recipe to allocate first)? Essentially do you want this https://dbfiddle.uk/HrLfg5Pl or this https://dbfiddle.uk/UFmiUXVb or perhaps you want something more complex, like showing a tree structure? — Charlieface, Jun 01 '23 at 00:54
I love the cross apply trick, dint knew it before. The greather refactor was nice, but it's only for sql server 2022. I'll refactor it to an IFF to improve readability tho. Basically what i need to know is which recipes i need to execute and how many times for being able to fulfill the original recipe according to the stock that is missing. Both solutions have the same problem, which is the repeated ingredient (red lid) not adjusting it's value properly because the top most recipe allocated the resources. In the fiddles the output with red lid on remaining should have been: 176 or 1. — Santiago Jose Gianotti, Jun 01 '23 at 18:44

Handling Stock Adjustment in Recursive CTE for Recipes with Shared Ingredients

0 Answers0