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:UsersSteveDocumentsGitHubAdventofCodeDay 2 - Wrappinginput.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


Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate