Walking Through the Advent of Code Day 2

Time to celebrate my friends! We’ve made it all the way to Day 2!

On day 2, we are asked to gather data from a series of games and to see which of those are possible given a specific number of dice for a few colors. Fun times!

As always, the first thing we have to do is import the data into our SQL Server. Since there are only 100 rows of data this time, many of the ways that I suggested in Walking Through the Advent of Code Day 1 are available. If you’re feeling brave enough, you can easily type them all in yourself – though I wouldn’t suggest it.

The first thing I want to do is separate everything into its different kinds of dice. Since we want to have the data broken out for red, blue, and green in the end, let’s create a red, blue, and green table. These should all be temp tables – since we don’t want to have them just lying about on our SQL Server for someone to get confused with later on.

DECLARE @pattern nvarchar(MAX) = N'%[0-9] blue%';
WITH Src AS
(    SELECT Game,
        SUBSTRING(results, PATINDEX(@pattern, results) - 1, 7) Val,
        STUFF(results, 1, PATINDEX(@pattern, results)+7, '') Txt
    FROM aocDay2
    WHERE PATINDEX(@pattern, results)>0
    UNION ALL
    SELECT
        Game,
        SUBSTRING(Txt, PATINDEX(@pattern, Txt) - 1, 7),
        STUFF(Txt, 1, PATINDEX(@pattern, Txt)+7, '')
    FROM Src
    WHERE PATINDEX(@pattern, Txt)>0   )
select REPLACE(Game, 'Game ', '') AS Game, RTRIM(LTRIM(REPLACE(Val, ' blue', ''))) AS BlueRolls
INTO #BlueGame
FROM Src

You’ll notice here that I defined a pattern to search for. I’m actually looking for a number and the string “blue”. Can this number be greater than 10 – meaning 2 digits? Absolutely, but it still has to have 1 number.

I then create everyone’s favorite thing to write ever – a recursive cte. This allows me to go through a string multiple times to find every example of the phrase I’m looking for and output it to my temp table.

Within this cte, I use functions we got very good with in previous posts – POSINDEX and SUBSTRING – and add another one to the mix – STUFF! Thankfully, I’ve talked about the use of the STUFF function before here and here so long-time readers of my blog know about how helpful this function can be.

I leave it up to the reader to do the same for the Red and Green tables. Some slight variations must be made for each of those tables, but I know everyone is smart enough to figure those out.

Once we have all of those tables set up, we need to figure out what games are impossible to play given the amount of dice. I know what you’re thinking here… “But, Sherpa, you must have misread the question! It asks for how many games are possible!”

Yes, we’re asked how many games are possible in the end. The problem is that since we have broken them into separate groups, we have to determine which games are impossible for each group. For example, if the red table shows games 1 and 3 are impossible, but games 3 and 7 are impossible for the blue table – we would only say that have 3 games that are impossible between them.

What I did here was to place all games that were impossible into a new temp table – #Impossible – for each type of dice. Once that is complete, I found which games were still possible by using my tally table – which I know everyone has a version of. If not, you can use this great site as an example.

Using that tally table we find what is in the tally table that is not in our #Impossible table.

SELECT Games From Tally
EXCEPT
SELECT Games FROM #Impossible;

Now, to make it even more challenging – and to force you to read – the creators of #AdventOfCode want you to sum up the IDs of the games that are possible.

And with that, we have come to the end of another portion of coding for today. Join us tomorrow when we go over another section of #AdventOfCode.