Testing – The Final Query
Today I want to finish the journey into testing you SQL code. I started with a customer request for a report of the history of the last 12 months of hiring broken out by department. I chose to use the Adventureworks database in order to demonstrate the code.
First we created a table value function requirement to return 12 time segments, each having a start date/time for the first day of the period, and date/time column for the very last period of the month. In order to test a function performing correctly, we created a virtual table using a common table expression with the expected values. We were able to write a query to test the function to be generated. Once the function was created, we can validate it is correct by comparing it to the common table expression.
Now you are preparing to return the 12 month calculations for the final report. You can do one of two things. You can either create a CTE using the same technique I demonstrated for the Table Value Function that returns 12 months based on existing data in your database. The second option is to create data so you are pre-populating your expected results. Either way, you can create a CTE with the expected results, and compare the actual query to assert its accuracy.
To make the series more complete, I wish to include a query returning the final report results. I have chosen to return a separate row for each department/period in order to save space necessary to pivot the data into a single row with 12 monthly columns. Pivot queries are quite common in SQL Server if you wish to research how to do this elsewhere. However, most reporting tools also have pivoting capability built into them.
I am using the AdventureWorks database for sample data. Scanning the data in the database I found I had to go back to 12/1/2002 in order to produce data for our report, and even then, there are many periods with no data reported. Still, no data reported is a significant number as well. Here is my final query…
SELECT
Period,
SegmentStart,
Dept.Name AS DepartmentName,
COUNT(DISTINCT Emp.StartDate) as EmployeeStartCount
FROM TwelveMonthSegments_tfn ('12/1/2002') AS Periods
CROSS JOIN HumanResources.Department Dept
LEFT JOIN HumanResources.EmployeeDepartmentHistory Emp
ON emp.StartDate BETWEEN Periods.SegmentStart
and Periods.SegmentEnd
AND emp.DepartmentID = Dept.DepartmentID
GROUP BY Period, SegmentStart, Dept.Name
I hope you have enjoyed this short venture into testing your code. If you wish to perfect your craft as a software developer, you can look into how to automate tests for you code. It can even be done with database code and queries. Tomorrow we’ll ask the quetion, what is the real value of testing database queries, stored procedures or other objects. If you wish to share your perspective, write in to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Go Beyond SCOM Monitoring with Foglight for SQL Server
read more)
Featured Script
osp_check_backups
This script returns a list of all databases on the server and the date/type when the db was last backed up…. (read more)