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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
DECLARE @InputText VARCHAR(MAX); SELECT @InputText = REPLACE(FileText, CHAR(10), '') FROM OPENROWSET(BULK 'D:\AdventOfCode\Day02.txt', SINGLE_CLOB) UselessAlias(FileText) DECLARE @TotalLength INTEGER = LEN(@InputText), @LengthNoCR INTEGER = LEN(REPLACE(@InputText, CHAR(13), '')); DECLARE @TotalRows INTEGER = @TotalLength - @LengthNoCR; DECLARE @SizePerRow INTEGER = @TotalLength / @TotalRows; DECLARE @CRPosition INTEGER = CHARINDEX(CHAR(13), @InputText, @TotalLength / 2); WITH cte AS ( -- put the two parts together. Add 1000 to the second call to get unique ItemNumbers -- replace LF with an empty string, and then split on the CR. SELECT ItemNumber, Item FROM Sandbox.dbo.DelimitedSplit8K(SUBSTRING(@InputText, 1, @CRPosition), CHAR(13)) UNION ALL SELECT ItemNumber+1000, Item FROM Sandbox.dbo.DelimitedSplit8K(SUBSTRING(@InputText, @CRPosition+1, 8000), CHAR(13)) ), cte1 AS ( -- split the LxWxH format into it's parts. Convert them into integers. SELECT cte.ItemNumber, cte.Item, dt1.ItemNumber AS SubItemNumber, dt2.Item AS SubItem FROM cte CROSS APPLY Sandbox.dbo.DelimitedSplit8K(cte.Item, 'x') dt1 CROSS APPLY (SELECT CONVERT(INTEGER, dt1.Item)) dt2(Item) ), cte2 AS ( SELECT dt1.ItemNumber, MAX(dt1.Item) AS Item, -- to carry the Item through -- pivot the SubItem based upon the SubItemNumber MAX(CASE WHEN dt1.SubItemNumber = 1 THEN dt1.SubItem ELSE NULL END) AS [Length], MAX(CASE WHEN dt1.SubItemNumber = 2 THEN dt1.SubItem ELSE NULL END) AS [Width], MAX(CASE WHEN dt1.SubItemNumber = 3 THEN dt1.SubItem ELSE NULL END) AS [Height], -- get the smallest area and lengths of two sides MIN(dt1.SubItem * dt2.SubItem) AS [MinArea], MIN(dt1.SubItem + dt2.SubItem) AS [MinLength] FROM cte1 dt1 CROSS JOIN cte1 dt2 WHERE dt1.ItemNumber = dt2.ItemNumber AND dt1.SubItemNumber <> dt2.SubItemNumber GROUP BY dt1.ItemNumber ), cte3 AS ( -- apply the formulas for each row SELECT *, (2*[Length]*[Width]) + (2*[Width]*[Height]) + (2*[Height]*[Length]) + ([MinArea]) AS WrappingPaperNeeded, ([Length]*[Width]*[Height]) + (2*[MinLength]) AS RibbonNeeded FROM cte2 ) -- summarize the two values needed. SELECT SUM(WrappingPaperNeeded) AS WrappingPaperNeeded, SUM(RibbonNeeded) AS RibbonNeeded FROM cte3; |
