x

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 '10 at 06:48 PM in Default

4efe27c0 gravatar image

4efe27c0
85 2 2 3

(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 '10 at 08:21 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

(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 '10 at 04:31 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x94
x44

asked: Mar 15 '10 at 06:48 PM

Seen: 1975 times

Last Updated: Mar 15 '10 at 06:48 PM