Best practices to ensure query results data correctness

Part of my work involves creating reports and data to be used as information for decision. The majority of the data is aggregated, like inventory, sales and costs totals from departments, and other dimensions.

When I am creating the reports, and more specifically, I am developing the SELECTs to extract the aggregated data from the OLTP database, I worry about mistaking a JOIN or a GROUP BY, for example, returning incorrect results.

I try to use some "best practices" to prevent me for "generating" wrong numbers:

  • When creating an aggregated data set, always explode this data set without the aggregation and look for any obvious error.
  • Export the exploded data set to Excel and compare the SUM(), AVG(), etc, from SQL Server and Excel.
  • Involve the people who would use the information and ask for some validation (ask people to help to identify mistakes on the numbers).
  • Never deploy those things in the afternoon - when possible, try to take a look at the T-SQL on the next morning with a refreshed mind. I had many bugs corrected using this simple procedure.

Even with those procedures, I always worry about the numbers.

What are your best practices for ensuring the correctness of the reports?

more ▼

asked Mar 15, 2010 at 06:48 PM in Default

avatar image

85 2 2 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You've covered a lot of what I do, especially checking with the business users. The one thing I'd add is in how you construct the queries. I find the root table and always validate that I'm pulling back from that table the information I should be. I tried to get a row count from that table early and then maintain that row count as I modify the query.

more ▼

answered Mar 15, 2010 at 08:21 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left

One extra thing that I will sometimes do, before even going near any T-SQL, is run through roughly on paper how the report will be generated with the person that wants it. This has led to changes a number of times for me in the past, and I really prefer to get change requests before there is anything to have to change!

more ▼

answered Mar 16, 2010 at 04:31 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 15, 2010 at 06:48 PM

Seen: 2312 times

Last Updated: Mar 15, 2010 at 06:48 PM

Copyright 2018 Redgate Software. Privacy Policy