Testing a Birthday Month Query

I ran across a post from a tester, wondering  how to write a query for birthday months. This makes sense, as I’m sure some businesses want to notify or track customers that have birthdays this month and give them something.

The post was good, with DDL and test data, and ignoring the discussion about database design, how would you test htis? If you look through the test data, surely you might decide to check for Jan, or Feb, etc., but are you use as the query might change that you’re checking everything?

This is where testing can help. I’d start with a simple test. One that uses some data and checks that nothing is returned.

CREATE OR ALTER PROCEDURE tTSQLTests.[test birthday queries for a Jan with no birthdays in range]
/*
Description:

Changes
11/22/2017
*/
AS
BEGIN
     -------------
     -- Assemble
     -------------
     DECLARE @begin DATETIME = '1994-01-01' ,
             @end DATETIME = '2000-01-01' ,
             @month TINYINT = 1;

    EXEC tSQLt.FakeTable @TableName = 'birthdays', @SchemaName = 'dbo';
     INSERT dbo.birthdays
     (
         cust_id ,
         cust_fname ,
         cust_lname ,
         cust_dob
     )
     VALUES
     (94, N'Jamie', N'Aguiar', '2017-06-02 00:00:00.000') ,
     (346, N'Keith', N'Brady', '1993-03-29 00:00:00.000') ,
     (361, N'Kelsea', N'Britto', '1994-03-25 00:00:00.000') ,
     (715, N'Tia', N'Delguidice', '1999-02-04 00:00:00.000') ,
     (994, N'Holly', N'Hamilton', '2017-11-12 00:00:00.000') ,
     (1110, N'ISABELLE', N'HYDER', '1993-04-06 00:00:00.000') ,
     (1295, N'RAELYN', N'LITTLE', '1995-02-15 00:00:00.000') ,
     (1403, N'ALLISON', N'RIPA', '1993-10-14 00:00:00.000') ,
     (1486, N'Rayvon', N'Miller', '1984-11-09 00:00:00.000') ,
     (1559, N'Alexandra', N'Sousa', '1989-09-17 00:00:00.000') ,
     (1897, N'Patrick', N'Snow', '1976-10-10 00:00:00.000') ,
     (1749, N'Justine', N'Zienowicz', '1998-03-12 00:00:00.000'),
     (2209,N'Brittany',N'Kosboski','1987-01-22 00:00:00.000')
     ;

    CREATE TABLE #Expected
     (
         cust_id INTEGER NOT NULL PRIMARY KEY ,
         cust_fname NVARCHAR(50) NOT NULL ,
         cust_lname NVARCHAR(50) NOT NULL ,
         cust_dob DATETIME NOT NULL
     );




    SELECT TOP 0
         cust_id ,
         cust_fname ,
         cust_lname ,
         cust_dob
     INTO #Actual
     FROM #Expected;

    ---------------
     -- Act
     ---------------
     INSERT #Actual
     SELECT cust_id ,
            cust_fname ,
            cust_lname ,
            cust_dob
     FROM birthdays
     WHERE cust_dob >= @begin
           AND cust_dob < @end
           AND MONTH(cust_dob) = @month;

    ---------------
     -- Assert    
     ---------------
     EXEC tSQLt.AssertEqualsTable @Expected = N'#expected' ,
                                  @Actual = N'#actual' ,
                                  @Message = N'The query doesn''t work';

END;


GO
-- GRANT EXECUTE ON tTSQLTests.[test birthday queries for a month] to userrole
EXEC tsqlt.run 'tTSQLTests.[test birthday queries for a Jan with no birthdays in range]'

Then I’d test for one birthday.

CREATE OR ALTER PROCEDURE tTSQLTests.[test birthday queries for a Jan with one birthdays in range]
/*
Description:

Changes
11/22/2017
*/
AS
BEGIN
     -------------
     -- Assemble
     -------------
     DECLARE @begin DATETIME = '1984-01-01' ,
             @end DATETIME = '2000-01-01' ,
             @month TINYINT = 1;

    EXEC tSQLt.FakeTable @TableName = 'birthdays', @SchemaName = 'dbo';
     INSERT dbo.birthdays
     (
         cust_id ,
         cust_fname ,
         cust_lname ,
         cust_dob
     )
     VALUES
     (94, N'Jamie', N'Aguiar', '2017-06-02 00:00:00.000') ,
     (346, N'Keith', N'Brady', '1993-03-29 00:00:00.000') ,
     (361, N'Kelsea', N'Britto', '1994-03-25 00:00:00.000') ,
     (715, N'Tia', N'Delguidice', '1999-02-04 00:00:00.000') ,
     (994, N'Holly', N'Hamilton', '2017-11-12 00:00:00.000') ,
     (1110, N'ISABELLE', N'HYDER', '1993-04-06 00:00:00.000') ,
     (1295, N'RAELYN', N'LITTLE', '1995-02-15 00:00:00.000') ,
     (1403, N'ALLISON', N'RIPA', '1993-10-14 00:00:00.000') ,
     (1486, N'Rayvon', N'Miller', '1984-11-09 00:00:00.000') ,
     (1559, N'Alexandra', N'Sousa', '1989-09-17 00:00:00.000') ,
     (1897, N'Patrick', N'Snow', '1976-10-10 00:00:00.000') ,
     (1749, N'Justine', N'Zienowicz', '1998-03-12 00:00:00.000') ,
     (2209, N'Brittany', N'Kosboski', '1987-01-22 00:00:00.000');

    CREATE TABLE #Expected
     (
         cust_id INTEGER NOT NULL PRIMARY KEY ,
         cust_fname NVARCHAR(50) NOT NULL ,
         cust_lname NVARCHAR(50) NOT NULL ,
         cust_dob DATETIME NOT NULL
     );

    INSERT #Expected
     (
         cust_id ,
         cust_fname ,
         cust_lname ,
         cust_dob
     )
     VALUES
     (2209, N'Brittany', N'Kosboski', '1987-01-22 00:00:00.000');

    SELECT TOP 0
         cust_id ,
         cust_fname ,
         cust_lname ,
         cust_dob
     INTO #Actual
     FROM #Expected;

    ---------------
     -- Act
     ---------------
     INSERT #Actual
     SELECT cust_id ,
            cust_fname ,
            cust_lname ,
            cust_dob
     FROM birthdays
     WHERE cust_dob >= @begin
           AND cust_dob < @end
           AND MONTH(cust_dob) = @month;

    ---------------
     -- Assert    
     ---------------
     EXEC tSQLt.AssertEqualsTable @Expected = N'#expected' ,
                                  @Actual = N'#actual' ,
                                  @Message = N'The query doesn''t work';

END;


GO
-- GRANT EXECUTE ON tTSQLTests.[test birthday queries for a month] to userrole
EXEC tSQLt.Run 'tTSQLTests.[test birthday queries for a Jan with no birthdays in range]';
EXEC tSQLt.Run 'tTSQLTests.[test birthday queries for a Jan with one birthdays in range]';

Next, I’d add a few more tests for other cases, perhaps checking that leap years, etc. run correctly.

Is this hard to setup? Well, I might argue that the time you spend examining result sets and checking random queries is about the same. Once I’ve written this test, which took about 10 minutes with a template, I can easily copy/paste the test, change the name and move data around. Ideally I’d stick this query in some procedure instead and run the test that way, which makes it easy for me to alter the query and re-run a ton of tests quickly.

The time saved isn’t in the initial development, but in the checking when I touch this code again, or tune it. What if I decided to replace the MONTH() check with a computed column, as suggested by some responders? Then my tests should still pass with a new query.

Testing builds better software. Not perfect, but better. And I can get better as developing over time by adding more tests.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s