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.

Hi Beth,

  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 steves@omaticsoftware.com.

Regards,

Steve Schindler

 

P.S.

 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);

 

WHILE @currentYear

BEGIN

IF @currentYear

BEGIN

SET @startDate = DATEADD(year,-@currentYear, @originalDate);

SET @endDate = DATEADD(month, 12, @startDate);

 

SELECT 

@totalcount = TOTALCOUNT, 

@yearcountmax = YEARCOUNTMAX

FROM

(

(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)

)

AS CY

 

SET @currentYear += 1;

SET @runningGiftCount = @runningGiftCount + @yearcountmax;

SET @runningAverage = @runningGiftCount / @currentYear;

 

IF @runningAverage >= @threshold AND @currentYear

BEGIN

SET @consecutiveYears =  @currentYear;

END

END

END

 

SELECT  @consecutiveYears;