## Advent of Code 2017 Day 5–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is day 5 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1. This is going to be a crazy looping item, since it will move through the list, relative to the current spot, and incrementing items, I know this won’t be good in SQL.

Still. Worth solving.

Let’s load the data. I’ll use a table, but first, I’ll also add an identity. This will help me number instructions and figure out what the next one is.

```CREATE TABLE Day5
( InstructionKey INT IDENTITY(1,1)
, Instruction INT)
GO```

There are issues with identities, but this is a great trick:

```CREATE VIEW Day5V
AS
SELECT d.Instruction FROM dbo.Day5 AS d
GO
-- reusable code
BULK INSERT Day5V FROM 'e:\Documents\GitHub\AdventofCode\2017\Day5\Input.txt' WITH (ROWTERMINATOR='\n')
GO```

Now I can get to work. Here’s the logic I used.

I wanted to first set some starting points. I have a counter (0 based, increment first). This determines how many times I jump around. I also need to track the current instruction key and the next key. And, of course, I need the instruction value.

The identity is the array index, or the instruction key (which place am I in). In this case, I’ll try to follow this logic.

Get the end (out of bounds, which is the max + 1). I loop until I get an jump outside of the end range. The loop does these items:

• Get the current instruction jump
• Set the next location to be the current key + the current jump
• Set the current instruction key to the next key
• loop

This seems to be what I need. On the test set, this worked fine. When I first set this up, I used this code:

```DECLARE @end INT ,
@CurrentInstructionKey INT = 1 ,
@Instruction INT ,
@NextInstructionKey INT ,
@counter INT = 0;
SELECT @end = MAX(InstructionKey) + 1
FROM dbo.Day5 AS d;

-- SELECT [end] = @end;

WHILE @CurrentInstructionKey < @end
BEGIN
SET @counter = @counter + 1;
SELECT @Instruction = Instruction
FROM Day5
WHERE InstructionKey = @CurrentInstructionKey;
SELECT @NextInstructionKey = @CurrentInstructionKey + @Instruction;
UPDATE dbo.Day5
SET Instruction = Instruction + 1
WHERE InstructionKey = @CurrentInstructionKey;
SET @CurrentInstructionKey = @NextInstructionKey;
--PRINT @CurrentInstruction
END;

SELECT Counter = @counter ,
[current] = @CurrentInstruction;```

When I ran this, it chugged for some time. I bet in Python or C#, which would solve quickly with arrays. With updates, it’s slow. Like minutes slow for 1074 rows.

However, it worked.

## Part II

In this part, this instructions are almost the same, but based on the current instruction value, we either increase or decrease the value. Not a big change. Our new update looks like:

```UPDATE dbo.Day5
SET Instruction = Instruction + CASE
WHEN @Instruction >= 3 THEN
-1
ELSE
1
END
WHERE InstructionKey = @CurrentInstructionKey;```

This also works, albeit slowly. I left this around 5:30 and went to the gym.

One of the easier puzzles.