In my previous post, I introduced you to the Advent of Code programming challenge, and explained how I’m going through the days, solving both parts of each puzzle. Continuing on, today we have day 15. In this challenge, we are working on the cookie recipe. Specifically, different ingredients impact a few different aspects / properties of the cookie (flavor, texture, capacity (to absorb milk) and durability (how well the cookie stay intact when full of milk). We are provided an input file that for each ingredient, breaks down how 1 teaspoon of that ingredient impacts the various aspects. The input file is formatted like this:

Each cookie needs 100 teaspoons of the combination of ingredients. The task is to find the combination that when considering the number of teaspoons of the ingredients and how this impacts the properties of the cookies, gives the highest score for the cookie. The score is determined  by adding together, for each property, all the the ingredients. Negative amounts of the total are considered zero, and then the properties are multiplied together to determine the score.

The first step will be to load the input file, separate the lines, and then from each line extract out the various parts. For that, we proceed as we have through most of these puzzles –  loading the data into a temporary table, and then determining the position of various characters in the line with the CHARINDEX function and extracting this data with the SUBSTRING function. The CROSS APPLY is just introducing those columns into the query so that they can be subsequently used in multiple places, while only being calculated once. Note that several of the CHARINDEX calls use the optional third parameter which specifies the starting position. In these cases, the code is looking for a comma, so we are specifying the position of the last comma + 1 as the starting position for the next comma:

In the next phase, I use a virtual tally table of the numbers 1-100, and cross join this to itself once for each ingredient, and the resulting numbers are the teaspoons to be used for each ingredient. Since the number of teaspoons used needs to equal 100, the code only gets the values where the four numbers add up to 100. Additionally, a ROW_NUMBER is calculated for each row. This will be used to be able to perform aggregations and to group upon. Next, each property needs to be added up across all ingredients (the property for that ingredient multiplied by the number of teaspoons used for that ingredient).  This requires performing a CROSS JOIN to the input file. Finally, all of the properties are multiplied across each other (a zero is substituted for negative sums), and the highest one is returned with the MAX function.

For part 2, you are asked to restrict the cookies to 500 calories. What is the score of the highest scoring cookie that is 500 calories?

To handle this modification, two minor changes are needed. Since the calories are already being stored in the input table, no changes are necessary in that section. In cte2, we need to calculate the calories for the teaspoons used and to sum up the calories across all of the ingredients, and in the final query we need to add a predicate to only get the max for cookies with 500 calories. To make these changes, insert lines 1-5 below between lines 69-70 of cte2, and add line 5 to the end:

Now that the cookie recipe has been mastered, day 15 is complete. Come back Monday for day 16.