SQL Sample

Essentially every piece of work I've done involves a relational database. I've designed hefty schemas for regional online coupon sites, handling visually constructed, user-created joins for Army Mapper, and created another backend that handles all the trappings that surround the creation of an email client.

But what you want to see is some code. It's actually much harder to work within the bounds of an existing schema, often full of non-normalized tables and data duplication, than it is to create a new one, so I'll start there. In fact, I'll use a rock-solid schema, but one that was being asked to do something it'd been explicitly designed not to do. The goal in this example was to add client requested functionality without breaking anything existing in the schema.

For this example, I believe it is long enough after working at Blackbaud for me to share this little bit of SQL, especially since the fix is obsolete with version 7.6. Raiser's Edge (RE), Blackbaud's flagship product, is software that allows non-profits to track donors, sending them mail solicitations, keeping track of what sorts of appeals each donor appreciates, even some support for seating donors attending fundraiser dinners, with the right modules. Of course the amount a donor has given would be an important thing to track, and RE does that well, gift by gift, from first to most recent.

One thing that RE did not do, at least not in any way that was viewable in the interface until version 7.6 (with a corresponding schema change), was keep a historical record of adjustments to those gifts. If someone accidently wrote that Jane Doe had donated $300 when she had donated $3000, after the adjustment to the original gift had been made, the original $300 gift dropped off of the face of the planet.

Raiser's Edge was not designed to be used as a historical financial managment tool; that was what The Financial Edge did and does. Regardless, RE was powerful enough many people wanted to have historical, auditable gift reporting, if only for a few key reports.

That's where these views come in handy. Using them to create a SQL view that replaced one gift table would seamlessly create audit-level historical tracking with RE, something Product Development at Blackbaud said was impossible to do. They did not know they were lying, apparantly, as they changed RE's database schema in version 7.6, not even at a major version change, which added the feature and, well, broke this fix literally a week or so after I made it. So though the fix was used on a number of reports, we didn't test it much beyond seeing that, "It works!" If there are outlier cases, I wouldn't be horribly surprised, but it's still good stuff.

In any event, it does make for interesting SQL, and added historical adjustment reporting to any legacy RE 7.5 or lower report by changing one table name in the report's construction. Very efficient, done in the database as it should have been, and actually pretty quick, too. Probably worth reading the Developer Documentation even if the SQL is too product-specific to spend much time reviewing.

Link to developer documentation and the views:

Developer Documentation for Adjustment Views (surprisingly easy reading)
    bbps_vwFirstAdjustmentSplits.sql
    bbps_vwAdjustmentSplits.sql
    bbps_vwNegativeAdjustmentSplits.sql
    bbps_vwFinalAdjustmentSplits.sql
    bbps_vwPreAdjustmentSplits.sql