Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2006
    Posts
    4

    Unanswered: 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.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you only need 3 columns, this should get you started:
    Code:
    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.

  3. #3
    Join Date
    Oct 2006
    Posts
    4
    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.

  4. #4
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by kellaro
    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.
    Last edited by vich; 10-24-06 at 19:23.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.
    Code:
    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 #tConsecutive
    The 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

  7. #7
    Join Date
    Oct 2006
    Posts
    4
    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

  8. #8
    Join Date
    Oct 2006
    Posts
    4
    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

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Check the code in red... It adds a new t alias (for "this") that enumerates the contributions between the beginning and the end.
    Code:
    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

  10. #10
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Another piece of genuine delicate art work,the real SQL smarties,man ...superb Pat !!
    We always learn a lot from you
    ...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •