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)

--)

--go

---- load data

--bulk insert Day2_WrappingPresents

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

--go

---- check

---- select * from Day2_WrappingPresents

--go

-- break this down to get the dimensions

with cteSplit (d, el, sw, sh)

as

(

select

dimensions

, 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

as

(select

d

, 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

as

( select

d

, 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

end

, 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

end

, 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

end

from cteDimensions

)

, cteFinal

as

(

select

d

, area = (2 * small * middle) +

(2 * small * large) +

     (2 * middle * large)

, slack = (small * middle)

from cteOrder

)

select

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.