As I continue through the Advent of Code, albeit slowly, I’m solving each puzzle 3 days. I worked through 6 of them in Python before moving on to other languages, and this is my chance to catch up with both PowerShell and T-SQL. I likely won’t post all solutions, but I was having fun rewriting code, so here are the ways I looked at things.

Note: You should try this on your own. I logged into the AventofCode with GitHub and things worked great for me.

Go on, give it a try.

I’ll wait.

Solutions coming.

## Python

I started here, using iterations, which are very powerful in Python. In this case, I took advantage of the multiple variable assignment in Python to enumerate the array and get each value and index. I think use a comparison to determine if I add or subtract one. Finally, an IF returns the current index if I hit –1. I should probably have a break in there for efficiency as well.

def calculate_negative(directions):

start = 0

for i, c in enumerate(directions):

if c == '(':

start += 1

else:

start -= 1

if start == -1:

print(i)

## PowerShell

This was a bit trickier for me. I wasn’t sure how to work with a string and pull out values. I did some searching and ran across the .ToCharArray function. That doesn’t feel like the best way to do this, but I decided to use it.

The rest of the function is similar and gave me the correct answer, so there you go.

$count = 0

$floor = 0

foreach ($c in $input.ToCharArray())

{

if ($c -eq '(') {$floor += 1}

elseif ($c -eq ')') {$floor -= 1 }

$count += 1

if ($floor -eq -1)

{

$count

break

}

}

## T-SQL

A more complex situation here, given that we need to work through a string, calculating a running total. I broke the input up using a tally table and a splitter with substring. This was fairly easy, and not complex as I was just getting individual charaters.

Then it was a simple running total of the CTE to get me the totals at each point in time. This worked well, but I needed the first –1 total.

Finally, I used the outer query to get the min value out of the code and use that, which gave me the lowest value where the –1 occurred.

WITH tally (n)

AS

( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) a(n) -- 10

CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) b(n) -- x 10 = 100

CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) c(n) -- x 10 = 1000

CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) d(n) -- x 10 = 10000

)

, direction (n, d)

AS

(

SELECT tally.n

, d = CASE WHEN SUBSTRING(@input, n, 1) = '(' THEN 1 ELSE -1 end

FROM tally

), currfloor

as

(SELECT

d.n

, 'currentfloor' = SUM(d.d) OVER (ORDER BY d.n ROWS UNBOUNDED PRECEDING)

FROM direction d

)

SELECT MIN(currfloor.n)

FROM currfloor

WHERE currentfloor = -1