For this post i worked on second puzzle in the Adventofcode series. This wasn’t as challenging as the first one, but still very doable with TSQL.

Part 1 of the puzzle  was as below:

The elves are running low on wrapping paper, and so they need to submit an order for more. They have a list of the dimensions (length l, width w, and height h) of each present, and only want to order exactly as much as they need.

Fortunately, every present is a box (a perfect right rectangular prism), which makes calculating the required wrapping paper for each gift a little easier: find the surface area of the box, which is 2*l*w + 2*w*h + 2*h*l. The elves also need a little extra paper for each present: the area of the smallest side.

For example:

• A present with dimensions 2x3x4 requires 2*6 + 2*12 + 2*8 = 52 square feet of wrapping paper plus 6 square feet of slack, for a total of 58square feet.
• A present with dimensions 1x1x10 requires 2*1 + 2*10 + 2*10 = 42square feet of wrapping paper plus 1 square foot of slack, for a total of 43 square feet.

All numbers in the elves’ list are in feet. How many total square feet of wrapping paper should they order?

Solution: The input was about 1000 lines long, in the format of axbxc, with a , b and c being numbers for each of the three dimensions. It was too long to stick into an insert statement, so I put it into a text file and imported that text file into a table. I then parsed the string to get the 3 numbers out as length,breadth and width, different fields in the same table. Don’t think that part is really necessary as it is simple TSQL. For  getting the answer to the puzzle, I used below query:

SELECT SUM( 2*(length*breadth)

+ 2*(Breadth*Width)

+ 2*(Width*Length)

+ (IIF(Length*Breadth > Breadth*Width, IIF(BREADTH*width > Width*Length, WIDTH*Length, Breadth*Width),IIF(LengTH*Breadth > Width*Length, Width*Length, Length*Breadth))

)) FROM [dbo].[listofcodes]

 

For part 2: 

The elves are also running low on ribbon. Ribbon is all the same width, so they only have to worry about the length they need to order, which they would again like to be exact.

The ribbon required to wrap a present is the shortest distance around its sides, or the smallest perimeter of any one face. Each present also requires a bow made out of ribbon as well; the feet of ribbon required for the perfect bow is equal to the cubic feet of volume of the present. Don’t ask how they tie the bow, though; they’ll never tell.

For example:

• A present with dimensions 2x3x4 requires 2+2+3+3 = 10 feet of ribbon to wrap the present plus 2*3*4 = 24 feet of ribbon for the bow, for a total of 34 feet.
• A present with dimensions 1x1x10 requires 1+1+1+1 = 4 feet of ribbon to wrap the present plus 1*1*10 = 10 feet of ribbon for the bow, for a total of 14 feet.

How many total feet of ribbon should they order?

Solution: This needed a table valued UDF that returns the sum of the least two values given 3 values. Again, simple TSQL which I don’t think is worth a cut and paste. The final solution was as below. I learnt something from this. I always assumed that a table valued UDF would return something (null if there was no value) – but there was a bug in mine which caused it to return nothing for some rows. When the cross apply returned fewer rows than main table it led me to investigate and figure this out – This was an interesting new discovery. But otherwise, the solution was simple.

SELECT SUM((2*minvalue)+(length*breadth*width))

FROM [dbo].[listofcodes] l CROSS APPLY dbo.[udf_summingsides](length,l.breadth,l.width) f

On to the next one…am not sure I’d be blogging every one of these since several of them are seriously procedural in solution. But when I find one that is TSQL Friendly will get to it again. Thank you for reading.