This one gives you a 1000 rows of package dimensions. You calculate the surface area needed for wrapping paper, plus some ‘slack’ that is the area of the two smallest dimensions. The first part is easy, the second – how do you figure out the two smallest numbers when represented as “4x2x10”?

First part was to load into a table in the easiest/laziest way possible. I copied the values to Notepad++, saved it to disk, and then:

create database AdventDay2
go
use AdventDay2
go

--import the rows and add a fake pkey
create table Packages (width int, height int, length int)
go
bulk insert Packages from 'c:\andy\packagesizes.txt' with (fieldterminator = 'x')
go
alter table Packages add RowID int identity (1, 1) primary key
go

I looked at the built- in functions, how could I pick two of the three columns in each row? CASE would work, but ugly. Same for IIF. Feels like there must be a trick there, but it’s not one I can figure out. How would I do it if it were rows instead of columns? How to get into columns? I decided to look up the syntax for unpivot (which I rarely use) to get the rows, then use the OVER to rank within each set of dimensions. If you can follow this, the rest is easy.

--we need the smallest and next smallest value to calculate the "slack" per the puzzle. Unpivot to convert it to rows, then use the windowing 
--function to order by size. RowNbr = 1 will be the smallest value in the group, RowNbr=2 will be the next. We don't care about 3!
select RowID, DimensionType, DimensionValue, row_number() over (partition by rowid order by dimensionvalue) as RowNbr into PackageDims from (
select RowID, DimensionType, DimensionValue from packages unpivot (DimensionValue for DimensionType in ([width], [height], [length])) as PackageUnPivot) a

To build the final set I joined twice to my just created PackageDims table, once to get the smallest value and then again to get the next smallest value. From there it’s just a matter of rolling it all up.

–calc the results

select

sum(PackageArea + Slack) as TotalSquareFeetNeeded

from

(

select

width,

height,

length,

d1.dimensionvalue as SmallestDim,

d2.dimensionvalue as NextDim,

((2 * length * width) + (2 * width * height) + (2 * height * length)) as PackageArea,

(d1.dimensionvalue * d2.dimensionvalue) as Slack

from packages p

inner join packagedims d1 on p.rowid = d1.rowid and d1.rownbr = 1

inner join packagedims d2 on p.rowid = d2.rowid and d2.rownbr = 2

) Final

Looking it, maybe a CTE for PackageDims would look cleaner, or breaking those up into 2 CTE’s to see the steps.

Forgot there was a part 2, which is easy to get based on what I’ve done so far, just change the query a little for the two new measures I need:

select
 sum(PackageArea + Slack) as TotalSquareFeetNeeded, sum(RibbonToWrap + BowLength) as TotalRibbonNeeded
from 
(
 select 
 width,
 height,
 length,
 d1.dimensionvalue as SmallestDim,
 d2.dimensionvalue as NextDim,
 ((2 * length * width) + (2 * width * height) + (2 * height * length)) as PackageArea,
 (d1.dimensionvalue * d2.dimensionvalue) as Slack,
 (2 * d1.dimensionvalue) + (2 * d2.dimensionvalue) as RibbonToWrap,
 (height * width * length) as BowLength 
 from packages p
 inner join packagedims d1 on p.rowid = d1.rowid and d1.rownbr = 1
 inner join packagedims d2 on p.rowid = d2.rowid and d2.rownbr = 2
) Final