In my previous post, I introduced you to the Advent of Code programming challenge, and explained how I’m going through the days, solving both parts of each puzzle. Continuing on, today we have day 3. Since you can only see this if you are up to this level in the challenge, let me explain it to you. In this puzzle, Santa starts delivering packages at a location (based on an infinite two-dimensional grid), and gets directions for which house is next (via N/S/E/W) commands from his trusted elf. Unfortunately, this elf has started his Christmas celebration too early and has consumed too much eggnog, so some houses are being skipped, and others are being visited numerous times. For Part 1, your task is to determine how many houses receive at least one present.

The input file for this challenge is a random series of characters. Going to the North is represented by “^”; South by “v”; East by “>”; and West by “<“. The input file can be obtained from here.

Once again, I’m going to start off by using a virtual / dynamic tally table. This is used to split the string apart position by position. It will then evaluate the character to see if the X or Y offset needs to be adjusted. At this point, the code that I’m using looks like this:

For the next step, I’m going to use a version of the UPDATE statement that allows updating variables and rows at the same time – and the rows will be updated with the current value of the variables. Sounds kinda quirky, doesn’t it? Well, it’s been a supported syntax in SQL Server for like, forever. Many of those that use it have given it a slang name: “Quirky Update”. Basically, there are two supported syntaxes:

What we will be doing is to get the X/Y offset, and put this into a variable. Then, the X/Y position will be updated by adding the current value in the X/Y variable to the offset and storing it in the X/Y variable, and also storing it in that row. As the update progresses through the recordset, each row will be updated with the current calculation. This code also includes a “safety check” to ensure that the recordset it indeed updated in the desired order – if at any time the current row being updated is not the next sequentially numbered row, then a divide-by-zero error will be generated, causing the entire operation to fail.

In the manner that we’re using the UPDATE statement, we need to control the order of the rows being operated on. In order to do this, there are about a dozen rules that need to be followed… rules which aren’t documented by Microsoft. Thankfully, Jeff Moden (this really smart guy over at SQLServerCentral.com) has a great article that goes through all of this. So, read and heed the rules at: //www.sqlservercentral.com/articles/T-SQL/68467/.

The final part of the code is:

And there we go – a nice, efficient set-based solution for this first part of the puzzle.

In the second part, the following year Santa decides to create a roboticized version of himself (Robo-Santa), and the workload is split between the two Santas. The elf is still tipsy from the eggnog, and he is using last years directions… but this time the directions are sent to the two Santas alternatively. Again, the puzzle is to find out how many houses received at least one present. To handle this, I’ll add some variables for Robo-Santa, reset some of the variables, and then run a slightly different quirky update:

Here I introduces a second set of variables for Robo-Santa, and a SantaNbr variable to flip back and forth between which Santa is the current one. The quirky update is modified to calculate the different variables based upon which Santa is currently receiving the directions, and finally the current Santa is flipped back and forth for each row.