SQL Sample

I believe it is long enough after working at Blackbaud for me to share this little bit of SQL, especially since it's 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 until version 7.6, at least not in any way that was viewable in RE's frontend, 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 who called the Customizations team wanted to have historical, auditable gift reporting. It was a fair request.

That's where these views come in handy. The end result was a single SQL view that was a drop-in replacement for RE's gift table, and that view seamlessly created audit-level historical tracking with RE -- a function Product Development at Blackbaud said was impossible to do using the 7.5 and earlier database schema. They didn't know they were lying, apparantly, as they changed RE's database schema in version 7.6 (not even at a major version) to add historical gift reporting. The downside was that the major schema change meant reports with this fix would only work on 7.5 and earlier. (Worse, the new schema from Product Development prevented a number of old reports based on the previous RE schema from working on 7.6, and forced a good deal of report rewriting for our clients who wanted to use the new version of RE, but that's another story...)

The take-home is that I was able to non-invasively add historical gift reporting to RE 7.5 and earlier, and I did so in a way that any legacy RE 7.5 or lower report could take advantage just by changing one table name in the report's construction. It's an efficient fix, done in the database as it should have been, and actually pretty quick, too. Take that, Product Development! I could have saved you a ton of trouble!

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