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, at least not in any way that was considered viewable, until version 7.6 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.

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.

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.

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