We are implementing what I'm sure is a not-altogether-uncommon practice of expiring house credit. As I started to investigate what this would actually mean, it occurred to me that since on-account monies can be added and subtracted at any time, it's pretty difficult to look at a single constituent with house credit and determine when that house credit expires. You certainly can't do this with the On-Account Tracking report.
In order to provide something like a house credit statement for a customer, where the oldest unused credit can be determined with a date created and value remaining, I believe a report would need to keep a running balance for each house credit payment for a customer, and furthermore practice a "first-in-first-out" (FIFO) methodology of accounting for which house credits remain. (Basically, the notion that when house credits are spent, the oldest credit on the account is used.)
I'm pretty deep into the code already (hooray window functions), but has anyone else thought of/done this before?
I can't help with your code, but this is our policy on account credit. Our patrons' account credit is only valid for the same season. At the end of the season, the account credit is converted to a donation. We did make an exception this year and roll over the account credit to the 20-21 season.
How do you keep track of what credit is ready to convert? Or do you have non-overlapping sales periods for your seasons?
Our season goes through August (but we have very few events during July and August); sales for the following season usually start in April. Tickets aren't mailed out til mid-summer and exchanges don't happen until then, so no new account credit until that point. By then, we really have few events remaining in the prior season that they could use the credit for - and we have emailed each patron that has credit a push to use the it toward the end of spring, reminding them there are limited events to use the credit for and that unused credit will become a donation as of whatever the last event date is for that season. So, there is a bit of overlap and, after the new season goes on sale, we do have our ticket office staff add notes to the orders that generate account credit indicating which season generated the credit.