A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Monday, November 9, 2009

Registrations - Key Information

I worked today for almost two hours with a customer in California who was seeing unexpected information in a query I had written for them some time ago. There were three things wrong with the results, and I learned a lesson from each of them when I discovered the ways to correct them.

First, the query was showing incorrect Custom Field values. After double-checking that the Registrations attendee information was displaying the expected values, I started digging through the query and the tables to figure out what was going on. I spent almost 2/3 of the entire time looking for the cause of this problem. To make the long story short, what I discovered is that the [RGRegCatIndCust].Counter column is not a truly unique Primary Key column. I found that the same Counter value had been used for two different attendees' custom information answers in two different events. The information that was showing up in my query results were for the wrong attendee at the wrong event. In order to correct this problem I had to use both [RGRegCatIndCust].Counter and [RGRegCatIndCustSetup].RGEveCounter as limiters on the custom field values. Once I did that, the query displayed the expected values.

Second, the query was showing the same registrant twice. This was because the attendee had been assigned to the wrong Category at first, and when the attendee information was moved to the correct Category, the amount owed changed. A new invoice had been created to adjust the amount owed. Since my query was grouping by invoice, the second invoice created a second entry in the results. I had to go in and remove the grouping by invoice and then add aggregate functions to the SELECT column information that dealt with invoice info. Once that was done, the correct balances displayed. I learned to always use an in-line view for the Registrations invoice information, because of the way that I used GROUP BY to pivot the custom answers into columns.

Third, the query was returning the wrong number of results. It was 11 shy of the correct number of attendees. I found 10 of the missing 11 by using LEFT joins to the invoice-related tables instead of INNER joins to them. When an attendee had no balance due for registering, there was no invoice created, and therefore the INNER joins were eliminating those attendees. The final 1 person was trickier. In order to get the last person on the results, I had to re-do the FROM clause so that [RGRegCatInd] (the Attendee table) was at the root, making sure to do LEFT joins to all the subsequent tables. That was I was sure to get at least one row for each attendee. What I discovered after I made the change was that one person had registered via WebView and that somehow in the synchronization process the Registrant information was lost but the Attendee information remained. Therefore my original approach of using RGReg (the Registrant table) as the root table omitted the one person whose Registrant information was lost. After I got the final missing person's row in the results, I could readily see that the Registrant information was null.

It was a productive two hours, and I learned some tips that I will apply to all my future Registrations queries. And I will probably need to go back and change some of my prior queries as well.