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.
