Puzzles–T-SQL Tuesday #114

This month we have a very interesting topic from Matthew McGiffen. He gets back to the roots of the party with code by asking a question on puzzles. It’s a good question, and one that makes me think. I’m not a big puzzler, but I think I’ve solved a few.

Interesting Challenges

One of the things we’ve tried to do at SQLServerCental is come up with some ways to inspire you. We have articles, numerous questions to be answered, our Question of the Day, Crosswords, and more. I’d like to do more, but one thing I’ve found is that puzzles take a lot of time.

I have enjoyed some of the puzzles from the Advent of Code and Project Euler, which are good programming exercises. I’ve used Python and PowerShell to solve some of these, mostly to practice skills in building algorithms and implementing them.

Solving a Puzzle

One of the puzzles that I enjoyed solving was from the Advent of Code 2015, Day 2. This was a surface area problem, and one that reminded me of math class in high school. I always did enjoy that subject.

In any case, I solved the issue by loading some data into a table and then digging in with a  few CTEs to

Might not be the best solution, but it was one I enjoyed working out:

---- create table

--create table Day2_WrappingPresents


-- dimensions varchar(100)



---- load data

--bulk insert Day2_WrappingPresents

-- from 'C:\Users\Steve\Documents\GitHub\AdventofCode\Day 2 - Wrapping\input.txt'


---- check

---- select * from Day2_WrappingPresents


-- break this down to get the dimensions

with cteSplit (d, el, sw, sh)





, endlength = charindex('x', dimensions) - 1

, startwidth = charindex('x', substring(dimensions, charindex('x', dimensions),20)) + charindex('x', dimensions)

, startheight = len(dimensions) - charindex('x', reverse(dimensions)) + 2

-- , c1 = charindex('x', dimensions)

-- , c2 = charindex('x', dimensions, charindex('x', dimensions)+1) -

from day2_wrappingpresents d


, cteDimensions




, l = cast(substring(d, 1, el) as int)

, w = cast(substring(d, sw, sh-sw-1) as int)

, h = cast(substring(d, sh, len(d)) as int)

from cteSplit d


, cteOrder


( select


, small = case

when l <= w and l <= h then l

when w <= l and w <= h then w

when h <= l and h <= w then h


, middle = case

when (l >= w and l <= h) or (l <= w and l >= h) then l

when (w >= l and w <= h) or (w <= l and w >= h) then w

when (h >= l and h <= w) or (h <= l and h >= w) then h


, large = case

when l >= w and l >= h then l

when w >= l and w >= h then w

when h >= l and h >= w then h


from cteDimensions


, cteFinal





, area = (2 * small * middle) +

(2 * small * large) +

     (2 * middle * large)

, slack = (small * middle)

from cteOrder



sum(area + slack)

from cteFinal

-- drop table Day2_WrappingPresents

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

1 Response to Puzzles–T-SQL Tuesday #114

  1. Pingback: T-SQL Tuesday #114 Puzzle Party – Roundup | Matthew McGiffen DBA

Comments are closed.