Walking Through Advent of Code Day 5 Part 2

Part One of Day 5 had lots of reference tables. And, as always, our elves have changed the rules of the game with part 2.

This time, the meaning of the seed numbers is slightly changing on us. Instead of it only being 20 seeds we have to cross-reference (at least that’s how many seeds my input lists out), we now have 10 seeds and a rather large range of numbers to work with after each of those 10.

Thankfully, we can still use our Tally table to create yet another table with all of the seeds listed in it – yes, all of them!

To convert the data over to something useful for Part 2, I did the following code:

DROP TABLE IF EXISTS #Seeds;

SELECT TL.Value, 
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
INTO #Seeds
FROM Seeds S
CROSS APPLY STRING_SPLIT(TheList, ' ') TL

DROP TABLE IF EXISTS #SeedsList;

CREATE TABLE #SeedsList
(	SeedNum bigint NOT NULL,
	TheRange bigint NOT NULL);

DECLARE @SeedNum bigint,
	@Range bigint,
	@RowNum smallint = 1

WHILE @RowNum < (SELECT COUNT(1) FROM #Seeds)
BEGIN
	SELECT @SeedNum = Value FROM #Seeds WHERE RN = @RowNum
	SELECT @Range = Value FROM #Seeds WHERE RN = @RowNum + 1

	INSERT INTO #SeedsList
	SELECT @SeedNum, @Range

	SET @RowNum += 2
END

DROP TABLE IF EXISTS BigSeedList;
CREATE TABLE BigSeedList
( Seed bigint NOT NULL PRIMARY KEY);

INSERT INTO BigSeedList
SELECT t.n
FROM Tally t
JOIN #SeedsList s on t.n > s.SeedNum 
     AND t.n < s.SeedNum + s.TheRange
;

In the code block to place data into #Seeds, you’ll notice I use the windowing function ROW_NUMBER() with an unordinary OVER clause – (ORDER BY (SELECT NULL)). If you remember ROW_NUMBER() does require an ORDER BY be provided. If you don’t provide one – as is done in this case – then it just numbers the data in the order that it was fed in – hats off as always to Itzik Ben-Gan for alerting us to this goodness.

Next, I use a loop to quickly and easily create a temp table like we’re used to using for the range of seeds that we will need to create. All of this is needed because there is no way to do a STRING_SPLIT based on the second space in a string. (Microsoft – add this to the ever-growing list of requests!)

Finally, into the BigSeedList table – Why yes, they do call me the “Creative Namer”! – goes all of the seeds we will need to worry about for our question.

After this is complete, then we can run almost the same query we ran at the end of Part 1.

SELECT S.Seed, MIN(HL.Destination)
FROM BigSeedList S
JOIN SeedToSoilGoodness SS ON SS.Source = S.Seed
JOIN SoilToFertilizerGoodness SF ON SF.Source = SS.Destination
JOIN FertilizerToWaterGoodness FW ON FW.Source = SF.Destination
JOIN WaterToLightGoodness WL ON WL.Source = FW.Destination
JOIN LightToTemperatureGoodness LT ON LT.Source = WL.Destination
JOIN TemperatureToHumidityGoodness TH ON TH.Source = LT.Destination
JOIN HumidityToLocationGoodness HL ON HL.Source = LT.Destination 
GROUP BY TL.Value

As always, don’t expect this to be a quick query to run, especially since you’re running this for hundreds of thousands of seeds. Unless you have 1+ PB of RAM on your SQL Server – and no one else is using it – it will be several minutes until you get an answer.

And with that, my friends, we have come to the end of another day of #AdventOfCode. This process got to use a lot of data we found in Part 1 and we got to do a little manipulation of strings today. Overall, a good day of coding. Until next time y’all!

Today’s Advent Song may not be on your normal list of songs sung around Christmas Time, but it should be!