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 21. In this challenge,you received a present – a role playing game. In this game, you (the player) and your enemy (the boss) take turns attacking each other. You each start off with 100 “Hit Points”, and you inflict damage based upon the weapons that you have. You are shielded by the armor that you have. You must purchase one weapon to use, and you may purchase one armor. You may purchase a ring for each hand. The store has just one of each item. The list of items in the store, along with their cost, how much damage they inflict, and how much protection they provide (as armor) is at the above link. The first person whose hit point score drops to zero (or less) loses. The damage inflicted is equal to the damage of all items of the attacker minus the armor total of the defender, and this damage is reduced from the hit points. Each attack will diminish the defender’s hit points by at least a value of one. The input file for this puzzle provides you with the boss’s stats. For Part 1 of this puzzle, you need to find out what is the least amount of gold that you can spend to win.
When the input file is examined, you see that it has three lines, one each for hit points, damage and armor. Each line lists the item, and then the amount of units for that item, with a colon between them. Therefore, we can use the string splitter routine to separate the lines, and then separate the items from their values by splitting again on the colon. The values returned will be stored into variables for future use. This code is:
1 2 3 4 5 6 7 8 9 10 11 12 |
-- get the Boss information from the input file and store it in variables DECLARE @BossHitPoints INTEGER, @BossTotalDamage INTEGER, @BossTotalArmor INTEGER; SELECT @BossHitPoints = MAX(CASE WHEN ds.ItemNumber = 1 AND ds2.ItemNumber = 2 THEN ds2.Item ELSE NULL END), @BossTotalDamage = MAX(CASE WHEN ds.ItemNumber = 2 AND ds2.ItemNumber = 2 THEN ds2.Item ELSE NULL END), @BossTotalArmor = MAX(CASE WHEN ds.ItemNumber = 3 AND ds2.ItemNumber = 2 THEN ds2.Item ELSE NULL END) FROM OPENROWSET(BULK 'D:\AdventOfCode\Day21.txt', SINGLE_CLOB) InputFile(FileText) CROSS APPLY Sandbox.dbo.DelimitedSplit8K(REPLACE(InputFile.FileText, CHAR(10), ''), CHAR(13)) ds CROSS APPLY Sandbox.dbo.DelimitedSplit8K(ds.Item, ':') ds2 WHERE ds.Item > ''; |
The next section of code builds a common table expression (cte) for each weapon, armor and ring that is available. Note that for the armor and rings, I’ve added rows with 0 points to handle not purchasing those items. These ctes are (note that this will not run until all of the remaining sections are put together).
13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
WITH Weapons AS ( -- list of all weapons SELECT * FROM (VALUES ('Dagger', 8, 4, 0), ('Shortsword', 10, 5, 0), ('Warhammer', 25, 6, 0), ('Longsword', 40, 7, 0), ('Greataxe', 74, 8, 0) ) dt(name, Cost, Damage, Armor) ), Armor AS ( -- list of all armor SELECT * FROM (VALUES ('Leather', 13, 0, 1), ('Chainmail', 31, 0, 2), ('Splintmail', 53, 0, 3), ('Bandedmail', 75, 0, 4), ('Platemail', 102, 0, 5), ('No Armor', 0, 0, 0) -- handle buying no armor ) dt(name, Cost, Damage, Armor) ), Rings AS ( -- list of all rings SELECT * FROM (VALUES ('Damage +1', 25, 1, 0), ('Damage +2', 50, 2, 0), ('Damage +3', 100, 3, 0), ('Defense +1', 20, 0, 1), ('Defense +2', 40, 0, 2), ('Defense +3', 80, 0, 3), ('No Rings1', 0, 0, 0), -- handle buying no rings for one hand ('No Rings2', 0, 0, 0) -- handle buying no rings for both hands (can have a no rings on each hand) ) dt(name, Cost, Damage, Armor) |
In the next cte, a list of all possible weapons with their costs, damage and armor scores are created. The actual weapon information is not necessary, just the values. This is accomplished by performing a CROSS JOIN between the three store items ctes. The rings cte is cross joined an additional time to handle a ring on each hand. Additionally, four CROSS APPLYs are performed. The first sums up the totals of the cost, damage and armor scores for the Player. The second calculates the damage to both the Player and Boss for that battle. The third adjusts the minimum damage to 1 in case it is less than 1. The fourth calculates the number of plays that the Boss and Player have for this battle by dividing the number of hit points (100) by the number of plays. Since this may return a fraction, and that fraction keeps the player alive for the next play, this is rounded up to the next integer value with the CEILING function. Additionally, the number of hits is handled as a numeric with a decimal position because in SQL Server, when an integer is divided by another integer, the result will be an integer with the fractional part truncated. A SELECT DISTINCT is performed because we only need to run the battle once for each condition:
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
( -- get the distinct list of battles that need to be evaluated SELECT DISTINCT Player.Cost, Player.Damage, Player.Armor, Plays.BossPlays, Plays.PlayerPlays FROM Weapons w CROSS JOIN Armor a CROSS JOIN Rings r1 CROSS JOIN Rings r2 -- calculate what the player's cost, damage and armor settings are CROSS APPLY (VALUES (w.Cost + a.Cost + r1.Cost + r2.Cost, w.Damage + a.Damage + r1.Damage + r2.Damage, w.Armor + a.Armor + r1.Armor + r2.Armor)) Player(Cost, Damage, Armor) -- calculate what the damage will be to the boss and to the player CROSS APPLY (VALUES (Player.Damage - @BossTotalArmor, @BossTotalDamage - Player.Armor)) DamagePre(ToBoss, ToPlayer) -- each play has a minimum damage of 1, so if the damage is < 1 set to 1 CROSS APPLY (SELECT CASE WHEN DamagePre.ToBoss < 1 THEN 1 ELSE DamagePre.ToBoss END, CASE WHEN DamagePre.ToPlayer < 1 THEN 1 ELSE DamagePre.ToPlayer END) Damage(ToBoss, ToPlayer) -- calculate the number of plays the boss and player can each have for the battle CROSS APPLY (VALUES (CEILING(100.0 / Damage.ToBoss), CEILING(100.0 / Damage.ToPlayer))) Plays(BossPlays, PlayerPlays) WHERE r1.name <> r2.name ) |
Finally, the code returns the cost column from the first row where the number of Boss Plays is less than or equal to the number of Player Plays (since the player plays first, if they both would eliminate their opponent on that play, then the player eliminated the boss first):
68 69 70 71 72 |
SELECT TOP (1) Cost FROM cteBattles -- just get the battles where the player has more plays than the boss (or =, which means player beat boss since player plays first) WHERE BossPlays <= PlayerPlays ORDER BY Cost; |
And now we have the solution for Part 1. For Part 2, the challenge is to find the highest cost that the player can spend and still lose to the boss. All of the hard work has been done, and this requires only a minor change to the final query. Just replace the code starting at line 67 with this code:
68 69 70 71 72 |
SELECT TOP (1) Cost FROM cteBattles -- just the battles where the boss has more plays than the player WHERE BossPlays > PlayerPlays ORDER BY Cost DESC; |
Well, there we go, Day 21 has been solved. Come on back tomorrow to see how to solve Day 22.