I love tSQLt. It’s a good way to write tests that can determine if your code is actually working. Since I’m a fan of unit testing, I think using tests to verify your logic is great. What’s excellent with tSQLt is that I can verify a number of cases at once.
I ran across this post asking for help with a query. Given the sample data and results, I wrote this proc and test. In the test, my “Act” is calling a proc I wrote that executes the first post’s query.
CREATE OR ALTER PROCEDURE RunTimeTests AS BEGIN SELECT Taskid, MIN(StartTime), MAX(EndTime), DATEDIFF(MINUTE, MIN(StartTime), MAX(EndTime)) FROM TimeTests GROUP BY Taskid; END; GO EXEC tsqlt.NewTestClass @ClassName = N'tTimeTests' GO CREATE OR ALTER PROCEDURE [tTimeTests].[test calculation min max time from timetests] AS BEGIN -- assemble EXEC tsqlt.FakeTable @TableName = N'TimeTests', @SchemaName = N'dbo' INSERT into TimeTests VALUES (1, '2017-02-23 09:48:47.413',NULL ), (1, '2017-02-23 09:50:47.413', '2017-02-23 10:59:47.413' ), (1, '2017-02-23 09:49:47.413',Null ), (2, '2017-02-23 10:40:47.413','2017-02-23 11:55:47.413' ), (2, '2017-02-23 10:39:47.413', NULL ), (2, '2017-02-23 10:11:47.413','2017-02-23 11:30:47.413') CREATE TABLE tTimeTests.Expected ( taskid INT, Mindtime DATETIME2(3), maxtime DATETIME2(3), Minutes int) INSERT tTimeTests.Expected VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71) , (2, '2017-02-23 10:39:47.413', '2017-02-23 11:55:47.413', 76) SELECT * INTO tTimeTests.Actual FROM tTimeTests.Expected WHERE 1 = 0; -- act INSERT tTimeTests.Actual EXEC RunTimeTests; -- assert EXEC tsqlt.AssertEqualsTable @Expected = N'tTimeTests.Expected', @Actual = N'tTimeTests.Actual', @Message = N'Incorrect times' END
When I run this, it easily verifies the answer that the data is incorrect from the poster.
If I change my expected results:
INSERT tTimeTests.Expected VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71) , (2, '2017-02-23 10:11:47.413', '2017-02-23 11:55:47.413', 104)
and re-run the test, it succeeds.
Now, does this mean the developer wouldn’t make this mistake? After all, if you think you should be getting those results, you will struggle with the query.
It doesn’t help there. However, it does help if you modify this code later and start to have strange results. This also means that I can add in more rows to the data, even more cases, and determine if the procedure still works. If I’m trying to cover a dozen cases, it’s much easier to re-run a tSQLt test than manually looking through results.
Give tsqlt a try. It’s free, and if you have the SQL Toolbelt, you can get a GUI with SQL Test for executing your tests.