Using tSQLt to Find Min/Max Times

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.

2017-02-24 13_08_32-SQL Test - Microsoft SQL Server Management Studio

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.

2017-02-24 13_09_53-SQL Test - Microsoft SQL Server Management Studio

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.

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