Category: Blog

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
• Update the current jump to increment by 1
• 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.