Advent of Code, Day 16

2016-01-25 Wayne Sheffield No comments

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 16. In this challenge, you have received a wonderful gift from your Aunt Sue, and you want to send her a thank-you card. There’s just one small problem… you have 500 Aunt Sue’s, and you don’t know which one sent you the gift.

The gift turns out to be your very own “My First Crime Scene Analysis Machine” (MFCSAM), and it can what specific compounds are in a sample. According to the instructions, it can detect:

children, by human DNA age analysis.
cats. It doesn’t differentiate individual breeds.
Several seemingly random breeds of dog: samoyeds, pomeranians, akitas, and vizslas.
goldfish. No other kinds of fish.
trees, all in one group.
cars, presumably by exhaust or gasoline or something.
perfumes, which is handy, since many of your Aunts Sue wear a few kinds.

In order to determine which Aunt Sue sent you the gift, you analyze the wrapping from the gift, and the machine tells you the following:

children: 3
cats: 7
samoyeds: 2
pomeranians: 3
akitas: 0
vizslas: 0
goldfish: 5
trees: 3
cars: 2
perfumes: 1

children: 3

cats: 7

samoyeds: 2

pomeranians: 3

akitas: 0

vizslas: 0

goldfish: 5

trees: 3

cars: 2

perfumes: 1

Well, you know a bit about your Aunts, so you start making a list of what you do know. However, there are items that you just don’t know about. They’re not missing, you just don’t know the value.

For part 1 of this puzzle, you need to return the number of the Aunt Sue that sent you the gift. The input file is in the format:

Sue 1: goldfish: 6, trees: 9, akitas: 0

You can see how there are some items that the MFCSAM returned that are not listed in the input file. As previously mentioned, these are not missing – you just don’t know what that Aunt Sue has for that item.

The first part of the solution is to load the input file, and to get for each Aunt the quantity of each item. This solution starts off by loading the input file into a variable:

DECLARE @InputText VARCHAR(MAX);
SELECT  @InputText = REPLACE(FileText, CHAR(10), '')
FROM    OPENROWSET(BULK 'D:\AdventOfCode\Day16.txt', SINGLE_CLOB) InputFile(FileText)

DECLARE @InputText VARCHAR(MAX);

SELECT @InputText = REPLACE(FileText, CHAR(10), '')

FROM OPENROWSET(BULK 'D:\AdventOfCode\Day16.txt', SINGLE_CLOB) InputFile(FileText)

Since the delimited string splitter only works on strings up to 8000 characters, you check the length and find that it is over 20,000 characters. So you decide to load the input file in steps, performing up to 8000 characters at a time. First off, create a temporary table to hold these lines. Next, get the first 8000 characters in the @InputText variable, and determine where the last CHAR(13) is. Get the string from @InputText up to this point. Then the string is split by the CHAR(13) to separate it into rows, and then each row is examined for which Sue # this is, and which items that we know about her by utilizing the CHARINDEX and SUBSTRING functions. This data is inserted into the temporary table, and the text that was just analyzed is removed from the beginning of @InputText. This process continues as long as @InputText has > 1 character (since we are not doing that final CHAR(13), @InputText will end up with just that in it):

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (
    RowID       INTEGER IDENTITY PRIMARY KEY CLUSTERED
,   RowData     VARCHAR(250)
,   Sue         INTEGER
,   Children    INTEGER
,   Cats        INTEGER
,   Samoyeds    INTEGER
,   Pomeranians INTEGER
,   Akitas      INTEGER
,   Vizslas     INTEGER
,   Goldfish    INTEGER
,   Trees       INTEGER
,   Cars        INTEGER
,   Perfumes    INTEGER
);

DECLARE @TempStr    VARCHAR(8000),
        @TempStrLen INTEGER;;
WHILE LEN(@InputText) > 1 -- one because it will have that final CHAR(13)
BEGIN
    -- reverse this string so that you can find the CHAR(13) closest to but prior to the 8000 character of @InputText
    SELECT @TempStr = REVERSE(SUBSTRING(@InputText, 1, 8000));
    -- once you find this, get the rest of the string and reverse it again to be back in the original layout
    SELECT @TempStr = REVERSE(SUBSTRING(@TempStr, CHARINDEX(CHAR(13), @TempSTr), 8000));
    -- get the length of this string
    SET @TempStrLen = LEN(@TempStr);

    -- insert this string into the temp table. Get the Sue value at the same time.
    INSERT INTO #temp (RowData, Sue, Children, Cats, Samoyeds, Pomeranians, Akitas, Vizslas, Goldfish, Trees, Cars, Perfumes)
    SELECT  ca1.Item
    ,       SUBSTRING(ds.Item, 4, SueEndPos-4)
    ,       Children    = CASE WHEN ca2.Children > 0     THEN SUBSTRING(ca1.Item, ca2.Children     + 10, CHARINDEX(',', ca1.Item, ca2.Children)      - ca2.Children       - 10) ELSE NULL END
    ,       Cats        = CASE WHEN ca2.Cats > 0         THEN SUBSTRING(ca1.Item, ca2.Cats         + 6,  CHARINDEX(',', ca1.Item, ca2.Cats)          - ca2.Cats           - 6 ) ELSE NULL END
    ,       Samoyeds    = CASE WHEN ca2.Samoyeds > 0     THEN SUBSTRING(ca1.Item, ca2.Samoyeds     + 10, CHARINDEX(',', ca1.Item, ca2.Samoyeds)      - ca2.Samoyeds       - 10) ELSE NULL END
    ,       Pomeranians = CASE WHEN ca2.Pomeranians > 0  THEN SUBSTRING(ca1.Item, ca2.Pomeranians  + 13, CHARINDEX(',', ca1.Item, ca2.Pomeranians)   - ca2.Pomeranians    - 13) ELSE NULL END
    ,       Akitas      = CASE WHEN ca2.Akitas > 0       THEN SUBSTRING(ca1.Item, ca2.Akitas       + 8,  CHARINDEX(',', ca1.Item, ca2.Akitas)        - ca2.Akitas         - 8 ) ELSE NULL END
    ,       Vizslas     = CASE WHEN ca2.Vizslas > 0      THEN SUBSTRING(ca1.Item, ca2.Vizslas      + 9,  CHARINDEX(',', ca1.Item, ca2.Vizslas)       - ca2.Vizslas        - 9 ) ELSE NULL END
    ,       Goldfish    = CASE WHEN ca2.Goldfish > 0     THEN SUBSTRING(ca1.Item, ca2.Goldfish     + 10, CHARINDEX(',', ca1.Item, ca2.Goldfish)      - ca2.Goldfish       - 10) ELSE NULL END
    ,       Trees       = CASE WHEN ca2.Trees > 0        THEN SUBSTRING(ca1.Item, ca2.Trees        + 7,  CHARINDEX(',', ca1.Item, ca2.Trees)         - ca2.Trees          - 7 ) ELSE NULL END
    ,       Cars        = CASE WHEN ca2.Cars > 0         THEN SUBSTRING(ca1.Item, ca2.Cars         + 6,  CHARINDEX(',', ca1.Item, ca2.Cars)          - ca2.Cars           - 6 ) ELSE NULL END
    ,       Perfumes    = CASE WHEN ca2.Perfumes > 0     THEN SUBSTRING(ca1.Item, ca2.Perfumes     + 10, CHARINDEX(',', ca1.Item, ca2.Perfumes)      - ca2.Perfumes       - 10) ELSE NULL END
    FROM    Sandbox.dbo.DelimitedSplit8K(@TempStr, CHAR(13)) ds
    CROSS APPLY (VALUES (CHARINDEX(':', ds.Item), ds.Item + ',')) ca1(SueEndPos, Item)
    CROSS APPLY (VALUES (CHARINDEX('children', ca1.Item),
                         CHARINDEX('cats', ca1.Item),
                         CHARINDEX('samoyeds', ca1.Item),
                         CHARINDEX('pomeranians', ca1.Item),
                         CHARINDEX('akitas', ca1.Item),
                         CHARINDEX('vizslas', ca1.Item),
                         CHARINDEX('goldfish', ca1.Item),
                         CHARINDEX('trees', ca1.Item),
                         CHARINDEX('cars', ca1.Item),
                         CHARINDEX('perfumes', ca1.Item))
                ) ca2(Children, Cats, Samoyeds, Pomeranians, Akitas, Vizslas, Goldfish, Trees, Cars, Perfumes)
    WHERE   ds.Item > '';
    -- remove @TempStr from @InputText.
    SET @InputText = SUBSTRING(@InputText, @TempStrLen, 50000);
END;

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp;

CREATE TABLE #temp (

RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED

, RowData VARCHAR(250)

, Sue INTEGER

, Children INTEGER

, Cats INTEGER

, Samoyeds INTEGER

, Pomeranians INTEGER

, Akitas INTEGER

, Vizslas INTEGER

, Goldfish INTEGER

, Trees INTEGER

, Cars INTEGER

, Perfumes INTEGER

);

DECLARE @TempStr VARCHAR(8000),

@TempStrLen INTEGER;;

WHILE LEN(@InputText) > 1 -- one because it will have that final CHAR(13)

BEGIN

-- reverse this string so that you can find the CHAR(13) closest to but prior to the 8000 character of @InputText

SELECT @TempStr = REVERSE(SUBSTRING(@InputText, 1, 8000));

-- once you find this, get the rest of the string and reverse it again to be back in the original layout

SELECT @TempStr = REVERSE(SUBSTRING(@TempStr, CHARINDEX(CHAR(13), @TempSTr), 8000));

-- get the length of this string

SET @TempStrLen = LEN(@TempStr);

-- insert this string into the temp table. Get the Sue value at the same time.

INSERT INTO #temp (RowData, Sue, Children, Cats, Samoyeds, Pomeranians, Akitas, Vizslas, Goldfish, Trees, Cars, Perfumes)

SELECT ca1.Item

, SUBSTRING(ds.Item, 4, SueEndPos-4)

, Children = CASE WHEN ca2.Children > 0 THEN SUBSTRING(ca1.Item, ca2.Children + 10, CHARINDEX(',', ca1.Item, ca2.Children) - ca2.Children - 10) ELSE NULL END

, Cats = CASE WHEN ca2.Cats > 0 THEN SUBSTRING(ca1.Item, ca2.Cats + 6, CHARINDEX(',', ca1.Item, ca2.Cats) - ca2.Cats - 6 ) ELSE NULL END

, Samoyeds = CASE WHEN ca2.Samoyeds > 0 THEN SUBSTRING(ca1.Item, ca2.Samoyeds + 10, CHARINDEX(',', ca1.Item, ca2.Samoyeds) - ca2.Samoyeds - 10) ELSE NULL END

, Pomeranians = CASE WHEN ca2.Pomeranians > 0 THEN SUBSTRING(ca1.Item, ca2.Pomeranians + 13, CHARINDEX(',', ca1.Item, ca2.Pomeranians) - ca2.Pomeranians - 13) ELSE NULL END

, Akitas = CASE WHEN ca2.Akitas > 0 THEN SUBSTRING(ca1.Item, ca2.Akitas + 8, CHARINDEX(',', ca1.Item, ca2.Akitas) - ca2.Akitas - 8 ) ELSE NULL END

, Vizslas = CASE WHEN ca2.Vizslas > 0 THEN SUBSTRING(ca1.Item, ca2.Vizslas + 9, CHARINDEX(',', ca1.Item, ca2.Vizslas) - ca2.Vizslas - 9 ) ELSE NULL END

, Goldfish = CASE WHEN ca2.Goldfish > 0 THEN SUBSTRING(ca1.Item, ca2.Goldfish + 10, CHARINDEX(',', ca1.Item, ca2.Goldfish) - ca2.Goldfish - 10) ELSE NULL END

, Trees = CASE WHEN ca2.Trees > 0 THEN SUBSTRING(ca1.Item, ca2.Trees + 7, CHARINDEX(',', ca1.Item, ca2.Trees) - ca2.Trees - 7 ) ELSE NULL END

, Cars = CASE WHEN ca2.Cars > 0 THEN SUBSTRING(ca1.Item, ca2.Cars + 6, CHARINDEX(',', ca1.Item, ca2.Cars) - ca2.Cars - 6 ) ELSE NULL END

, Perfumes = CASE WHEN ca2.Perfumes > 0 THEN SUBSTRING(ca1.Item, ca2.Perfumes + 10, CHARINDEX(',', ca1.Item, ca2.Perfumes) - ca2.Perfumes - 10) ELSE NULL END

FROM Sandbox.dbo.DelimitedSplit8K(@TempStr, CHAR(13)) ds

CROSS APPLY (VALUES (CHARINDEX(':', ds.Item), ds.Item + ',')) ca1(SueEndPos, Item)

CROSS APPLY (VALUES (CHARINDEX('children', ca1.Item),

CHARINDEX('cats', ca1.Item),

CHARINDEX('samoyeds', ca1.Item),

CHARINDEX('pomeranians', ca1.Item),

CHARINDEX('akitas', ca1.Item),

CHARINDEX('vizslas', ca1.Item),

CHARINDEX('goldfish', ca1.Item),

CHARINDEX('trees', ca1.Item),

CHARINDEX('cars', ca1.Item),

CHARINDEX('perfumes', ca1.Item))

) ca2(Children, Cats, Samoyeds, Pomeranians, Akitas, Vizslas, Goldfish, Trees, Cars, Perfumes)

WHERE ds.Item > '';

-- remove @TempStr from @InputText.

SET @InputText = SUBSTRING(@InputText, @TempStrLen, 50000);

END;

At this point, we just need to analyze the data to see which Aunt Sue matches all of the criteria that the MFCSAM returned. However, since we don’t know all of the information for each Aunt, we need to assume that if the data isn’t there, that it does match. This will be accomplished with the ISNULL function, and if the item is NULL then it will return the value that was returned from the MFCSAM. The code for this is:

SELECT  * 
FROM    #temp
WHERE   ISNULL(Children, 3) = 3
AND     ISNULL(Cats, 7) = 7
AND     ISNULL(Samoyeds, 2) = 2
AND     ISNULL(Pomeranians, 3) = 3
AND     ISNULL(Akitas, 0) = 0
AND     ISNULL(Vizslas, 0) = 0
AND     ISNULL(Goldfish, 5) = 5
AND     ISNULL(Trees, 3) = 3
AND     ISNULL(Cars, 2) = 2
AND     ISNULL(Perfumes, 1) = 1;

SELECT *

FROM #temp

WHERE ISNULL(Children, 3) = 3

AND ISNULL(Cats, 7) = 7

AND ISNULL(Samoyeds, 2) = 2

AND ISNULL(Pomeranians, 3) = 3

AND ISNULL(Akitas, 0) = 0

AND ISNULL(Vizslas, 0) = 0

AND ISNULL(Goldfish, 5) = 5

AND ISNULL(Trees, 3) = 3

AND ISNULL(Cars, 2) = 2

AND ISNULL(Perfumes, 1) = 1;

Great – now we know which Aunt sent us the gift. However, before sending the thank-you card, you notice in the instructions for the MFCSAM that for some of the items, the MFCSAM returns a range and not a specific value. Specifically, the cats and trees readings indicate that there are more than this number, and the pomeranians and goldfish readings indicate that there are fewer than this number. This is a relatively simple change to the code… for the cats and trees, change the value operator to a greater than, and change the value used in the ISNULL function to be higher than the number being tested for. Likewise, for the pomeranians and golffish, change the operator to a less than, and change the value used in the ISNULL function to be lower than the number being tested for:

SELECT  * 
FROM    #temp
WHERE   ISNULL(Children, 3) = 3
AND     ISNULL(Cats, 8) > 7
AND     ISNULL(Samoyeds, 2) = 2
AND     ISNULL(Pomeranians, 2) < 3
AND     ISNULL(Akitas, 0) = 0
AND     ISNULL(Vizslas, 0) = 0
AND     ISNULL(Goldfish, 4) < 5
AND     ISNULL(Trees, 4) > 3
AND     ISNULL(Cars, 2) = 2
AND     ISNULL(Perfumes, 1) = 1;

SELECT *

FROM #temp

WHERE ISNULL(Children, 3) = 3

AND ISNULL(Cats, 8) > 7

AND ISNULL(Samoyeds, 2) = 2

AND ISNULL(Pomeranians, 2) < 3

AND ISNULL(Akitas, 0) = 0

AND ISNULL(Vizslas, 0) = 0

AND ISNULL(Goldfish, 4) < 5

AND ISNULL(Trees, 4) > 3

AND ISNULL(Cars, 2) = 2

AND ISNULL(Perfumes, 1) = 1;

And here is my T-SQL solution for Advent of Code, Day 16. Come on back tomorrow and see my next solution.

Advent of Code, SQL none

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Blogroll

Advent of Code, Day 16

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply

Wayne Sheffield

Recent Posts

Top Posts

Archives

Categories

Tags

Blogroll

Advent of Code, Day 16

Share this:

Like this:

Related

Leave a Reply to let me know how you liked this postCancel reply