Quick coding logic sort of question: What's the most efficient way to calculate the number of consecutive years that a given customer has donated to the opera?
I'm looking at T_CONTRIBUTION, and could count years by specific campaigns or even tie it to T_CAMPAIGN and use that table's fyear column, but finding all customers who have donated for 10 consecutive years (as I am sometimes asked - though not enough to force myself to put together some sort of code I can reuse) manages to elude me from a logic standpoint.
As I sat pondering such things as "well, if I take the fyear of a customer's latest donations and subtract the fyear of the next latest fyear of their donations and the result is one, then I've got a consecutive year..." but then it occurred to me I might be missing the forest for the trees and decided to ask you clever folk. Any suggestions?
Thankfully, I await responses.
BONUS TRIVIA: Pigeons are the only bird that can swallow water without having to crane their heads up.
I recall a similar quandry I had a few years ago. Unfortunately, I don't think I still have the code around but I remember the general solution. I used the SQL PIVOT query. A very brief overview:
You can find a tutorial on the topic here: http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx
PIVOT can entail a lot of hard coding of values within the PIVOT clause, but if you get really ambitious you can find some ways to build those strings through other methods.
Happy hunting.