Measuring consecutive years

Hi there.

I work for a charitable organization, am new to this form (and sql programming) and trying to create a flag for unique records indicating the number of consecutive years a donor has given.

I have create a sample db idenifying donor, giving year and total pledges with multiple donor records existing for multiple years having donated.

CREATE TABLE mygifts06 (Donor_id varchar (10), Gift_yr nvarchar (4), Tot_pledges numeric (16,2))

INSERT INTO mygifts06 (Id,Gift_yr,Pledges)
SELECT 155758,2005,15.00 UNION ALL
SELECT 155759,2004,25.00 UNION ALL
SELECT 155758,2004,40.00 UNION ALL
SELECT 155757,2005,100.00 UNION ALL
SELECT 155758,2002,30.00 UNION ALL
SELECT 155758,2001,120.00 UNION ALL
SELECT 155755,2003,15.00 UNION ALL
SELECT 155758,2006,80.00 UNION ALL
SELECT 155757,2003,65.00 UNION ALL
SELECT 155759,2005,400.00

For the above dataset, I am trying to create the following output

Donor_id 2_consec_gifts 3_consec_gifts 4 consec_gifts
--- ----- ----- -----
155755 0 0 0
155757 0 0 0
155758 1 1 0
155759 1 0 0

Do I need to use a cursor for this task? I lack experienced in using cursors is there an alternative method someone could suggest?

Thanks in advance.If you only need 3 columns, this should get you started:

select a.donor_id, a.gift_yr, b.gift_yr, c.gift_yr
from (select donor_id, max(gift_yr) as gift_yr from mygifts06 group by donor_id) a left join
mygifts06 b on a.donor_id = b.donor_id and a.gift_yr = b.gift_yr + 1 left join
mygifts06 c on a.donor_id = c.donor_id and a.gift_yr = c.gift_yr + 2 and b.gift_yr is not null

As a side note, I changed your gift_yr column to int datatype, because it is a little hard to work with as nvarchar.|||Thanks MCrowley. This is useful and does get me started in the right direction :) ...however I need to know whether or not an individual has ever made a 2, 3, 4, etc., years consecutive donations not just in relation to their last gift. So for example 155755 might have not made a donation in the two consecutive years prior to 2003, but they may have donated 3 years straight bewteen 1994 and 1996.

155755 2003 NULL NULL
155757 2005 NULL NULL
155758 2006 2005 2004
155759 2005 2004 NULL

I will keep fiddling with what you have given me. Thanks again.|||Thanks MCrowley. This is useful and does get me started in the right direction :) ...however I need to know whether or not an individual has ever made a 2, 3, 4, etc., years consecutive donations not just in relation to their last gift. So for example 155755 might have not made a donation in the two consecutive years prior to 2003, but they may have donated 3 years straight bewteen 1994 and 1996.

155755 2003 NULL NULL
155757 2005 NULL NULL
155758 2006 2005 2004
155759 2005 2004 NULL

I will keep fiddling with what you have given me. Thanks again.
I'd use a (dreaded) cursor loop of a table select ordered by ID then Year. Declare 2 variables for the highest-delta and current-delta.

Maybe there's a way to do this with a single query but I can't think of it off hand, and a loop would be so much simpler to program. Something simple like this wouldn't take long to run anyway.

To achieve the output you described above, you could create a nice query, but that output doesn't really count the greatest number of CONSECUTIVE years.|||Assuming integer years, try this
select a.Doner_id
,'2_consec_gifts'=max(case when b.Doner_id is null then 0 else 1 end)
,'3_consec_gifts'=max(case when c.Doner_id is null then 0 else 1 end)
,'4_consec_gifts'=max(case when d.Doner_id is null then 0 else 1 end)
from mygifts06 a
left join mygifts06 b on b.Doner_id=a.Doner_id and b.Gift_yr=a.Gift_yr+1
left join mygifts06 c on c.Doner_id=a.Doner_id and b.Gift_yr=a.Gift_yr+1 and c.Gift_yr=a.Gift_yr+2
left join mygifts06 d on d.Doner_id=a.Doner_id and b.Gift_yr=a.Gift_yr+1 and c.Gift_yr=a.Gift_yr+2 and d.Gift_yr=a.Gift_yr+3
group by a.Doner_id
order by a.Doner_id|||This isn't exactly the way that I'd implement a solution (I'd use functions to simplify changes in the definition of "continuous"), but this is a pure SQL, set based solution.CREATE TABLE #tConsecutive (
donor_id INT
, contribution DATETIME
)

INSERT INTO #tConsecutive (
donor_id, contribution
) SELECT 1000, '1960-01-01' UNION
SELECT 1000, '1961-02-01' UNION
SELECT 1000, '1962-03-01' UNION
SELECT 1000, '1963-04-01' UNION
SELECT 1000, '1965-01-01' UNION
SELECT 1000, '1966-02-01' UNION
SELECT 1001, '1971-02-01' UNION
SELECT 1001, '1972-02-01' UNION
SELECT 1001, '1975-01-01'

SELECT b.donor_id, b.contribution, e.contribution
FROM #tConsecutive AS b
JOIN #tConsecutive AS e
ON (e.donor_id = b.donor_id
AND e.contribution = (SELECT Min(y.contribution)
FROM #tConsecutive AS y
WHERE y.donor_id = b.donor_id
AND b.contribution < y.contribution
AND NOT EXISTS (SELECT *
FROM #tConsecutive AS x
WHERE x.donor_id = b.donor_id
AND y.contribution < x.contribution
AND x.contribution <= DateAdd(month, 18, y.contribution))))
WHERE NOT EXISTS (SELECT *
FROM #tConsecutive AS z
WHERE z.donor_id = b.donor_id
AND DateAdd(month, -18, b.contribution) < z.contribution
AND z.contribution < b.contribution)

DROP TABLE #tConsecutiveThe b alias is the beginning of a consecutive range, and the e alias is the end of that range. I used 18 months in order to allow some drift in dates, but you may want to just code a "before" and an "after" function to give you more flexibility.

-PatP|||Thank you both pdreyer & pat, both of your solutions are great and allow me to solve the task at hand. Thanks again for everyone's input. Great forum!

Richard|||Hey Pat;

There is something I am not getting. Since I want to examine consecutive contributions by calendar year, I've adjusted the data slightly to easily inject into your programming model. I've entered actual observation year, and the output correctly displays ID#1001 consecutively giving between 1988 and 1992 and then again between 1994 and 1995. However for ID#1000 is displays incorrectly consecutive giving between 1985 and 1988 -- it should register consecutive gifts between 1987 and 1988 & 1990 and 1991 only with 1985 and 2006 being outliers.

I've tried changing the values in the DATEADD function to 12 but that just seems to make things worse...any thoughts?

Thanks in advance.

CREATE TABLE #tConsecutive (
donor_id INT
, contribution DATETIME
)

INSERT INTO #tConsecutive (
donor_id, contribution
) SELECT 1000, '1985-01-01' UNION
SELECT 1000, '1987-01-01' UNION
SELECT 1000, '1988-01-01' UNION
SELECT 1000, '1990-01-01' UNION
SELECT 1000, '1991-01-01' UNION
SELECT 1000, '2006-01-01' UNION
SELECT 1001, '1988-01-01' UNION
SELECT 1001, '1989-01-01' UNION
SELECT 1001, '1990-01-01' UNION
SELECT 1001, '1991-01-01' UNION
SELECT 1001, '1992-01-01' UNION
SELECT 1001, '1994-01-01' UNION
SELECT 1001, '1995-01-01'

SELECT b.donor_id, b.contribution, e.contribution
FROM #tConsecutive AS b
JOIN #tConsecutive AS e
ON (e.donor_id = b.donor_id
AND e.contribution = (SELECT Min(y.contribution)
FROM #tConsecutive AS y
WHERE y.donor_id = b.donor_id
AND b.contribution < y.contribution
AND NOT EXISTS (SELECT *
FROM #tConsecutive AS x
WHERE x.donor_id = b.donor_id
AND y.contribution < x.contribution
AND x.contribution <= DateAdd(month, 18, y.contribution))))
WHERE NOT EXISTS (SELECT *
FROM #tConsecutive AS z
WHERE z.donor_id = b.donor_id
AND DateAdd(month, -18, b.contribution) < z.contribution
AND z.contribution < b.contribution)

DROP TABLE #tConsecutive|||Check the code in red... It adds a new t alias (for "this") that enumerates the contributions between the beginning and the end.CREATE TABLE #tConsecutive (
donor_id INT
, contribution DATETIME
)

INSERT INTO #tConsecutive (
donor_id, contribution
) SELECT 1000, '1960-01-01' UNION
SELECT 1000, '1961-02-01' UNION
SELECT 1000, '1962-03-01' UNION
SELECT 1000, '1963-04-01' UNION
SELECT 1000, '1965-01-01' UNION
SELECT 1000, '1966-02-01' UNION
SELECT 1001, '1971-02-01' UNION
SELECT 1001, '1972-02-01' UNION
SELECT 1001, '1975-01-01'

SELECT b.donor_id, b.contribution, e.contribution, t.contribution
FROM #tConsecutive AS b
JOIN #tConsecutive AS e
ON (e.donor_id = b.donor_id
AND e.contribution = (SELECT Min(y.contribution)
FROM #tConsecutive AS y
WHERE y.donor_id = b.donor_id
AND b.contribution < y.contribution
AND NOT EXISTS (SELECT *
FROM #tConsecutive AS x
WHERE x.donor_id = b.donor_id
AND y.contribution < x.contribution
AND x.contribution <= DateAdd(month, 18, y.contribution))))
JOIN #tConsecutive AS t
ON (t.donor_id = b.donor_id
AND t.contribution BETWEEN b.contribution AND e.contribution)
WHERE NOT EXISTS (SELECT *
FROM #tConsecutive AS z
WHERE z.donor_id = b.donor_id
AND DateAdd(month, -18, b.contribution) < z.contribution
AND z.contribution < b.contribution)

DROP TABLE #tConsecutive-PatP|||Another piece of genuine delicate art work,the real SQL smarties,man ...superb Pat !!
We always learn a lot from you ...:rolleyes: