Category: Blog

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is day 2 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1. Here is a look at Day 2, which deals with checksums of rows. However, it’s not just rows, but rows and columns.

Part I

In part 1, we are given a series of numbers in rows and columns, essentially a spreadsheet. We need to look at each row and compare all the values in columns.

This is slightly tricky for T-SQL. The input is a series of text values, so I cheated slightly and inserted those as rows into a table (I like tables). If I were taking this as a programming item, I’d have an import process, so I’ll do that here inline.

I have some idempotent logic here as I was testing and didn’t want to recreate tables and inserts, nor did I want to comment/uncomment things in/out.

IF NOT EXISTS( SELECT name FROM sys.sysobjects AS s WHERE name = ‘Day2’)
CREATE TABLE Day2
( DataRow VARCHAR(1000)
)
GO
IF (SELECT COUNT(*) FROM dbo.Day2 AS d) = 0
INSERT Day2 VALUES
( ‘179    2358    5197    867    163    4418    3135    5049    187    166    4682    5080    5541    172    4294    1397’),
( ‘2637    136    3222    591    2593    1982    4506    195    4396    3741    2373    157    4533    3864    4159    142’),
( ‘1049    1163    1128    193    1008    142    169    168    165    310    1054    104    1100    761    406    173’),
( ‘200    53    222    227    218    51    188    45    98    194    189    42    50    105    46    176’),
( ‘299    2521    216    2080    2068    2681    2376    220    1339    244    605    1598    2161    822    387    268’),
( ‘1043    1409    637    1560    970    69    832    87    78    1391    1558    75    1643    655    1398    1193’),
( ’90    649    858    2496    1555    2618    2302    119    2675    131    1816    2356    2480    603    65    128′),
( ‘2461    5099    168    4468    5371    2076    223    1178    194    5639    890    5575    1258    5591    6125    226’),
( ‘204    205    2797    2452    2568    2777    1542    1586    241    836    3202    2495    197    2960    240    2880’),
( ‘560    96    336    627    546    241    191    94    368    528    298    78    76    123    240    563’),
( ‘818    973    1422    244    1263    200    1220    208    1143    627    609    274    130    961    685    1318’)

The input might be different for you, so you’d have to do your own work.

Splitting a row of data into more rows seems like something easier done with the STRING_SPLIT function, so I’ll use that. This will give me a series of values from the table. I decided to use an inline TVF for this, since it’s a bit easier to read.

The trick here is that the data is tab separated, and \t doesn’t work in SQL Server. However, CHAR(9) works, so let’s use that. Here’s the function:

CREATE FUNCTION AdventChecksum ( @input NVARCHAR(200))
RETURNS TABLE
AS RETURN
SELECT diff = MAX(CAST(myint AS INT)) – MIN(CAST(myint AS INT))
  FROM (SELECT myint = CAST(value AS INT) FROM STRING_SPLIT(@input, CHAR(9))) AS ss
GO

I need to get the difference between the largest and smallest values. STRING_SPLIT will return strings, so I cast this to an INT, then use MAX() and MIN() for the row, subtract one from the other, and return that.

Now I have a way to get a row checksum, so let’s get the entire table. We’ll do that with a CROSS APPLY, and then sum up all the values returned:

SELECT SUM(b.diff)
  FROM day2 a
  CROSS APPLY dbo.AdventChecksum (a.DataRow) b

I guess it works, because I got the right value from a test set, and the puzzle shows as solved.

Part II

Part II adds a twist. Now I don’t need to just compare two values, I actually need to see if any of the values are equally divisible by the other. This means I need to compare all values against each other.

The STRING_SPLIT() function still works well here, however, I need all combinations of the values divided by each other. An evenly divisible set of numbers would have this pseudocode:

INT(a) / INT(b) = FLOAT(a) / FLOAT(b)

Or, there’s another way to look at this. The remainder is 0, so the remainder of the values, using the modulo (%) function, is 0.

One last trick, each number is divisible by itself, so let’s avoid those. The instructions don’t mention the possibility that the only values evenly divisible are matches, so we’ll assume that’s not the case. Checking inputs, this appears to be OK.

I’ll create a new function, where I CROSS JOIN the STRING_SPLIT to get all combinations.

CREATE FUNCTION AdventChecksum2 ( @input NVARCHAR(200))
RETURNS TABLE
AS RETURN
SELECT divmatch = CASE WHEN (CAST(a.value AS INT) % CAST(b.value AS INT)) = 0 AND a.value <> b.value
        THEN (CAST(a.value AS INT) / CAST(b.value AS INT))
      ELSE 0
      end
       FROM STRING_SPLIT(@input, CHAR(9)) a
   CROSS JOIN STRING_SPLIT(@input, CHAR(9)) b
GO

Now I cross apply as in Part I and sum the values, which works. The puzzle is solved.