Anyone have a good way to count the distinct number of fiscal years in which someone has given at least one gift during the last 10 years? I don't care about how consecutive those years are, I just want to distinguish someone who has given in 2 of the last 10 years from someone who has given in 9 of the last 10 years, etc. This is similar to the post on here about actions ("SQL Function to Count Specific Actions"), but I don't need to count the number of gifts per year, just a yes or no for each year.
Further complicating this is that there are some "gifts" in our database that don't count as gifts, so I need to be able to apply e.g. type, campaign and fund filters to the gifts before distinct counting the fiscal year. Also, our fiscal year runs July through June, so I can't just transform the gift date to a year and COUNTD the years.
If I were doing this in SQL Server Management Studio, I would use WITH xx AS (Select...) to create the set of "true" gifts first, then select from that set. I don't know how the custom SQL in RE/ScoreOmatic handles that kind of query though.
The SQL below should get you close to what you are looking for. There are several variables that you can change the values of to control things like what the year range is, the minimum gift amount to consider, and most importantly, the threshold variable, which allows you to count years in which no gift was given if the threshold remains above a specified level. It's not exactly what you are asking for, but if we work together I think we can get what you need.
You can easily add filters to the gift amount filter for campaign, fund, etc. I will be happy to work with you to achieve the desired results. We can then share that result on this forum to help others.
I can be contacted directly at firstname.lastname@example.org.
This is included as a sample SQL query in today's release of ScoreOmatic.
DECLARE @maxPerYr dec;
DECLARE @maxnumyears dec;
DECLARE @numyears int;
DECLARE @startYear int;
DECLARE @currentYear dec;
DECLARE @consecutiveYears dec;
DECLARE @yearNum dec;
DECLARE @totalCount dec;
DECLARE @yearCountMax dec;
DECLARE @runningAverage dec(18,2);
DECLARE @runningGiftCount dec;
DECLARE @startMonth int;
DECLARE @startDate as date;
DECLARE @endDate as date;
DECLARE @originalDate as date;
DECLARE @threshold dec;
DECLARE @giftAmount dec;
SET @maxperyr=2.0; --maximum number of gifts to count per year
SET @startyear = 2010; --year to start with
SET @startMonth = 1; --month to base year start date on
SET @threshold = .9; --minimum average required to count a year that contains no gifts
SET @giftAmount = 100; --minimum dollar amount of gifts to be considered
SET @numyears = 4; --number of years to consider
SET @currentYear = 0.0;
SET @runningGiftCount = 0.0;
SET @consecutiveYears = 0.0;
SET @originalDate = CAST(CONVERT(VARCHAR, @startYear) + '-' + RIGHT('0' + CONVERT(VARCHAR, @startMonth),2) + '-' + '01' AS Date);
SET @startDate = DATEADD(year,-@currentYear, @originalDate);
SET @endDate = DATEADD(month, 12, @startDate);
@totalcount = TOTALCOUNT,
@yearcountmax = YEARCOUNTMAX
(SELECT COUNT(ID) AS TOTALCOUNT, CASE WHEN CONVERT(decimal,COUNT(ID)) >= @maxperyr THEN @maxperyr ELSE CONVERT(decimal,COUNT(ID)) END AS YEARCOUNTMAX
FROM GIFT AS G WITH(NOLOCK)
WHERE G.DTE >= @startDate AND G.DTE
AND G.Amount > @giftAmount AND G.CONSTIT_ID = 189)
SET @currentYear += 1;
SET @runningGiftCount = @runningGiftCount + @yearcountmax;
SET @runningAverage = @runningGiftCount / @currentYear;
IF @runningAverage >= @threshold AND @currentYear
SET @consecutiveYears = @currentYear;