SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.


Filed under: Blog Tagged: syndicated, testing, tsqlt

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...