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 2. Since you can only see this if you are up to this level in the challenge, let me explain it to you. In this puzzle, Santa’s elves are running low on wrapping paper (part 1) and ribbon (part 2). The input is a list of box dimensions in the form LxWxH, with each box on a separate line (see the sample input here).

To determine the amount of wrapping paper needed for each box, the following formula is used: (2*l*w + 2*w*h + 2*h*l) plus the area of the smallest side. The amount of ribbon needed for each box is the shortest distance around it’s sides, plus the cubic feet of the volume of the box.

The first step in this puzzle is to get each line into a separate row,. For this, I use my favorite string-splitter function which can be found here. However, I immediately run into a small problem… this function has an input limitation of 8000 characters, and the string is larger than this. While you could use other methods to load this data (put it into a file, create a table for it, and then BCP the data from the file into the table and then work with it), I like to do things in as few steps as possible. So, I just just threw together some code to load a file using the OPENROWSET function and then split the string roughly in half (it splits it at a carriage return), and then to load the two parts.

After the file has been loaded, I then need to split the strings into it’s parts for length, width, height. Again, I turn to my favorite string-splitter function. Since the function returns the data as a varchar, I also convert it into an integer for later use. For each row in the input file, I now have three rows (one row each for length, width and height), so I then pivot the rows so that I am again with one row per set of data. During this pivot, I also get the smallest area of a side (for calculating the paper needed), and the smallest lengths of two sides (for calculating the ribbon needed). Note that I don’t use the PIVOT operator, instead I use the MAX/MIN functions with an embedded CASE statement. I find that this is typically faster, and if I need to ever make this code into dynamic SQL, it is easier this way. This method also allows for me to perform additional grouping at the same time, such as like here where I’m also getting other minimum calculations.

Now that I have all the necessary data, the formulas are applied to each row, and then finally they are summarized to get the final results.

My final code: