Tag: T-SQL

I like participating in the Advent of Code each year, though my participation often varies wildly as life gets in the way. Still, trying to solve some programming challenges is a good way of practicing your skills. If you’re competitive, you can try and see how quickly you can solve things and get onto the leaderboard.

One note, if you enjoy the challenges, support the cost of running the site. Sending $5 would make a difference to what I’m sure is a decent amount of effort and some costs. Plus, I’d certainly be happy to buy the author some sushi if I were sitting next to him, so why not send something during the holidays.

This year’s challenge is over, but you can still work through the challenges. In my case, I’ve gone through a few and hope to get to more in a few spare moments.

Testing Day 2

One of the things I’ve done in the past is see a challenge and then start to write some code. I’ve worked through the puzzles in PoSh, Python, and SQL, sometimes all three. When I think I’ve solved it, I often enter a result, which is wrong, and then code some more, repeating as needed.

This isn’t different from what I’ve done as an employee for a company, but I’ve also realized that the subtle design specification is sometimes mis-interpreted by me. In that case, I’ve essentially been bothering the “QA” people for no reason. It’s an application in this case, but still.

It would be better to have inputs and outputs specified and checked by the computer, which is way better at checking than I am. I decided to set up test harnesses after Day 1 (which was really easy) for the problems. Here’s Day 2.

Puzzle A

The first part of Day 2 is a puzzle about letters, asking you to compute a checksum based on whether any letters are repeated. This isn’t a complex set of instructions, but it would be easy to make a mistake. Across any number of sets, a human might have problems verifying the actual results.

Since the answer here is a single value, this lends itself to a test. I decided to start by creating a table and then loading the input data into the table. That’s something I often do, so the basics here were:

CREATE TABLE dbo.Day2

( Boxnumber INT

, boxid VARCHAR(100)

)

GO

INSERT dbo.Day2 (boxnumber,boxid)

SELECT  ca1.ItemNumber,
         ca2.Item

FROM    OPENROWSET(BULK 'e:\Documents\GitHub\AdventofCode\2018\Day2\input.txt', SINGLE_CLOB) dt(FileData)

CROSS APPLY dbo.Split(dt.FileData, CHAR(10)) ca1

CROSS APPLY (VALUES(REPLACE(ca1.Item, CHAR(13), ''))) ca2(Item);

Now that I had data, I can write a test. I like to use tsqlt, so I started there. Since I want something to test, I decided to start with a procedure that will hold my solution. Since I’ll code here, I can stub this out.

CREATE OR ALTER PROCEDURE Day2a

AS

BEGIN
     DECLARE @i INT = 1;
-- Solution goes here
 
RETURN @i

END

With this set up, we can now build a test. The basic outline for a test is Assemble an environment, Act on your code, Assert your results. Let’s follow this template.

The Assemble is easy. I’ll fake out my table of values and insert the test section from the calendar. I’ll also add the expected result, which is given in the puzzle as 12.

CREATE OR ALTER PROCEDURE tsqltests.[test Day2a]

AS

BEGIN
     ---------------
     -- Assemble
     ---------------
     DECLARE
         @expected INT = 12
       , @actual INT;
     EXEC tsqlt.faketable @TableName = 'Day2', @SchemaName = 'dbo';
     INSERT dbo.Day2
         (
             Boxnumber
           , boxid
         )
     VALUES
         (1, 'abcdef')
       , (2, 'bababc')
       , (3, 'abbcde')
       , (4, 'abcccd')
       , (5, 'aabcdd')
       , (6, 'abcdee')
       , (7, 'ababab');

The Act part is easy. I’ll call my procedure and get the result back.

---------------

-- Act

---------------

EXEC @actual = dbo.Day2a;

The Assert part is also easy. I’ll just compare my actual result to what I expected.

---------------

-- Assert    

---------------

EXEC tSQLt.AssertEquals
     @Expected = @expected
   , @Actual = @actual
   , @Message = N'An incorrect checksum calculation occurred.';

Once this is done, I’ll run it and it fails because my stub proc returns 1. Now to code the solution, which I can easily check by running my test. I can verify things work with a first change to my procedure.

CREATE OR ALTER PROCEDURE Day2a

AS

BEGIN
     DECLARE @i INT = 1;
SELECT @i = 12
RETURN @i
GO
EXEC tsqlt.run 'tsqltests.[test Day2a]';

That’s it, and the solution is to split out the box IDs, count the letters, and where there are repeats, tally those up.

Puzzle B

The second part of the puzzle is always a nice twist on the first part. In this case, I get a new set of IDs, which vary by a single character.I need to pick those two box IDs and return the common ones. A new solution needed, but only a slight change to the test.

First, we change the Assemble section because we have new results and inputs.

    ---------------
     -- Assemble
     ---------------
     DECLARE
         @expected VARCHAR(26) = 'fgij',
         @actual   VARCHAR(26);
    EXEC tsqlt.faketable @TableName = 'Day2', @SchemaName = 'dbo';
     INSERT dbo.Day2 (Boxnumber, boxid) VALUES

(1, 'abcde'),

(2, 'fghij'),

(3, 'klmno'),

(4, 'pqrst'),

(5, 'fguij'),

(6, 'axcye'),

(7, 'wvxyz')

Next, I need to change the ACT section. Since I can’t return a string from a procedure, I could use a function, but I’ll just add an OUTPUT parameter to my Act.

---------------

-- Act

---------------

EXEC dbo.Day2a @actual OUTPUT;

Lastly, I change the proc.

CREATE OR ALTER PROCEDURE Day2b
   @r VARCHAR(50) out

AS

That’s it.

Good luck solving the puzzles.