What are your best practices for ensuring correct SQL reports?

Question

Part of my work involves creating reports and data from SQL Server for use as decision information. Most of the data is aggregated, for example, inventory, sales and costs received from departments, and other dimensions.

When I create reports, and more specifically, I design SELECT to retrieve aggregated data from an OLTP database, I worry that you accept JOIN or GROUP BY, for example, returning incorrect results.

I am trying to use some “best practices” to prevent me from “generating” the wrong numbers:

• When creating an aggregate data set, always explode this data set without aggregation and look for an obvious error.
• Export the embedded data in Excel and compare SUM (), AVG (), etc. with SQL Server and Excel.
• Involve people who will use this information and ask for some kind of check (ask people to help identify errors in the rooms).
• Never deploy these things during the day - whenever possible, try looking at T-SQL the next morning with an updated mind. I have had many bugs fixed using this simple procedure.

Even with these procedures, I always worry about numbers.

What are your best practices for ensuring correct reports?

Answer 1

You think that you are filling your tables with test data that give known results and compare the results of your query with the expected results.

Answer 2

• Signed in writing

I found that one of the best practices is that both the reader / client and the developers are on the same (documented) page. Thus, when cryptic numbers appear (and they do), I can point to the specification in writing and say, “That's why you see this number. Would you like it to be different?”

• Testing, testing, testing

For seriously complex reports, we passed the test data up and down with the client until all numbers were correct and the client was satisfied.

• Borderline cases

We found a serious complex case in our reporting system, which turned everything upside down (on our side). What if the user creates a report (for example, the year-end of 2009), enters data for the new year, and then returns to create the same report? The data has changed, but this report should not. Thinking and working on these cases can save you a lot of suffering.

Answer 3

Write some automated tests.

We have quite a few service maintenance reports - we test them using Selenium. We use the test data page to transfer some known data to an empty database, then run the report and confirm that the numbers are as expected.

The assembly starts every time we register, so we know that we didn’t do anything stupid