This method requires the creation of a table commonly referred to as a Numbers table. It consists of one column named Number that is populated with integer values of 1 to 100000. The number of rows (numbers) required will be determined by what you are using it for. In this case 1000 would be enough, but because this table is used for many other uses we currently have 100,000 rows. This table is permanent and never changed unless you need to increase the number of rows in which case you would just drop the table and recreate it with the modified statement. In a near future version of ScoreOmatic we will automatically create this table if it does not exist. If your Organization chooses to create this table via SQL Server you need to grant Select privileges to the RE_ReadOnly database role for the table before it can be used in ScoreOmatic.

The statement to create the table is (there are many other methods, this is just one):

-------------------------------------------------
CREATE TABLE dbo.os_Numbers(Number int not null)
;WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65,536 rows
Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
Tally as (select row_number() over(order by C) as Number from Pass5)
INSERT os_Numbers
(Number)
SELECT Number
FROM Tally
WHERE Number ALTER TABLE os_Numbers ADD CONSTRAINT PK_OS_NUMBERS PRIMARY KEY CLUSTERED (Number)
-------------------------------------------------

The SQL Query can be changed to look at consecutive years within a given year range. It will return the constituent record ID once for each consecutive year starting with the first year the first year until the year is = the second year or no longer consecutive.
Based on the statement below, if a constituent gave in 2005,2006,2008,2009,2010, and 2014 you will get the ID 3 times, for 2008,09, & 10. If the points value in the profile for this query was 5,
the score would be 15.

The text to place in the SQL Query editor window in ScoreOmatic is:

-------------------------------------------------

LEFT OUTER JOIN(
SELECT DISTINCT G.CONSTIT_ID, YEAR(G.DTE) AS GIFTYEAR FROM GIFT AS G
LEFT OUTER JOIN NUMBERS AS N ON N.NUMBER = G.ID
WHERE G.CONSTIT_ID IS NOT
AND (SELECT MAX(DTE) FROM GIFT WHERE CONSTIT_ID = G.CONSTIT_ID) >= DATEADD(year,-1, GETDATE())
AND YEAR(G.DTE) >
(SELECT TOP 1 N.Number
FROM Numbers AS N
LEFT JOIN GIFT ON YEAR(GIFT.DTE) = N.Number AND GIFT.CONSTIT_ID = G.CONSTIT_ID
WHERE GIFT.DTE IS
AND N.Number AND N.Number >= 2002
ORDER BY N.Number DESC)) AS V1 ON V1.CONSTIT_ID = RECORDS.ID

-------------------------------------------------

As cool as the SQL statement is, I think I found a way to replicate consecutive giving scoring with queries. It may not be as efficient, but I think I have more control over it (because I'm really not a big user of raw SQL statements anymore).

Queries are nice because I can control what I count as "giving" in a year. If someones gives me $5 each year because they attended some small alumni event then I don't really count them as a "consecutive donor"... But if someone gives $500+ per year (I can also control for hard/soft credit) then I want to know about that person. All I did was this:

1. Created a Constituent query which finds everyone who a total of $500 or more in 2013 AND 2014. That's my "Consecutive Year Giving 2013/2014" query.

2. Repeat that, creating a similar query for each set of two years.. So my next query is "Consecutive Year Giving 2012/2013" and so fourth, moving back one year with each query.

If I put them in a Scoring profile and assign one point per year I essentially get the same result you did. So if a constituent gave $500 or more in 2005,2006,2008,2009,2010, and 2014 that person would get 3 points.

Make sense? I probably won't go much further back than 10 or 15 years... which only means 10 or 15 queries... Does that make sense?

More importantly, is that right? heh....

Hi Tom,

Thanks for the post, I love the thought of being able to do this without resorting to SQL Queries. I guess I was hung up on this scenario:

Queries: "Consecutive Year Giving 2013/2014", "Consecutive Year Giving 2012/2013", "Consecutive Year Giving 2011/2012", "Consecutive Year Giving 2010/2011", "Consecutive Year Giving 2009/2010"

Constituent A gives over $500 in 2009, 10, 11, 12, 13, and 14 = 5 points
Constituent B gives over $500 in 2010, 11, 12, 13, and 14 = 4 points (skipped 2009)
Constituent C gives over $500 in 2009, 10, 11, 13, and 14 = 4 points (skipped 2012)

My thought here was that ideally B would have more points because he has a longer chain of consecutive years of giving? I suppose we could also have additional criteria to remove a point if they didn't give in a recent year. So, subtract a point if they didn't give in 2013,12,11, or 10. A coworker, who was a long-time DBA at a small university, also suggested the idea of give extra points for those who gave at the height (nadir?) of the recession.

Another interesting idea, as I'm thinking about this, is that this might be a great way to identify long-time regular donors who have suddenly gone LYBUNT/SYBUNT. If the population you score is a query of those who are not deceased and haven't given recently, but they have a huge consecutive giving score, it might be worth looking into what can be done to recapture them.

Thanks Tom, great idea!

Jeff

This is exactly what we do! Only we are using more of a "consistent giving" approach rather than consecutive giving in some scoring. Basically we are giving some wiggle room to our donors so if they don't hit OUR definitions of a FYE due to a check date or a pledge form date, we will still count them. So we either do 15 month moving periods or we are doing 3 out of 5 years or something so that we aren't punishing our loyal, consistent donors that aren't consecutive based on our organization's time tables! Sometimes our priority dates are not the donors! Again, we are keeping all of these queries within RE and have not had to experiment with SQL yet, although I do understand that there is a lot of power in doing so. If only we had a dedicated staff member to do it

Great idea! I think this will give a pretty good approximation. It may give somewhat of a false positive if for example someone gave in 2005 and 2006 but then not in 2007-2008 but again in 2009-2010 they would get some points, - say they would get 2 points for consecutive years. It would give some data, but perhaps not a completely accurate picture ... not sure if this is making sense to my tired brain this morning, but perhaps others will chime in!

Jeff,

Hmm, I get what you're saying about Constituent B being slightly more valuable in your scenario... Of course, with ScoreOMatic you could solve that problem by adding another query which uses RE's Consecutive Years Giving total. So you could kind of award a "bonus point" for having 5 or more consecutive (unbroken) years of giving (though you can't only count certain amounts per year).

As Jennifer said we also give our donors some "wiggle room" with consecutive giving. We have people who sometimes miss a year by accident and then purposely make up for it the next year when they remember. I don't really want to penalize people for that. If you're looking at a 15 year period then anyone with a 13, 14 or 15 should still get "points" for being that dedicated :-)

In our case I didn't even bother filtering out specific gift types... I just want to capture the "intent to give"... so someone might pledge $1000 in one year and pay off $500 in the next year, but that's okay... That counts as "consecutive giving" in a very broad sense. They still thought about us each year so we want to know about them...