• Replies 11 replies
  • Subscribers 337 subscribers
  • Views 383 views
  • Users 0 members are here
Related

SQL code for consecutive years as donor

Clarke Weigle
$organization

Does anyone have lying around some SQL code that derives the number of consecutive years a constituent has been a donor?

Clarke

Parents
  • $organization

    i found my original code which is easier to use and follow. the solution is called Gaps and Islands, per the google machine.

    select
    ROW_NUMBER() over(partition by customer_no order by cont_amt desc) as sort_largest,
    ROW_NUMBER() over(partition by customer_no order by cont_dt desc) as sort_newest,
    ROW_NUMBER() over(partition by customer_no order by cont_dt asc) as sort_oldest,
    customer_no,
    cont_amt,
    cont_dt,
    isnull(fyear, case when DATEPART(MM,cont_dt) > 6 then cast(DATEPART(YYYY,cont_dt) as int) + 1 else cast(DATEPART(YYYY,cont_dt) as int) end) as fyear
    into #work1
    from
    T_CONTRIBUTION a
    join T_CAMPAIGN b on a.campaign_no = b.campaign_no
    where
    cont_amt > 0
    --order by
    --customer_no

    select customer_no, fyear from #work1 order by customer_no, fyear

    SELECT
    customer_no,
    fyear,
    fyear - dense_rank() OVER(partition by customer_no ORDER BY customer_no, fyear) AS grp
    FROM #work1
    order by customer_no, fyear


    SELECT
    ROW_NUMBER () over(partition by customer_no order by max(fyear) - min(fyear) +1 desc) as Sort,
    customer_no,
    MIN(fyear) AS startYr,
    MAX(fyear) AS endYr,
    max(fyear) - min(fyear) +1 as consYrs

    FROM (
    SELECT
    customer_no,
    fyear,
    fyear - dense_rank() OVER(partition by customer_no ORDER BY customer_no, fyear) AS grp
    FROM
    #work1
    --order by customer_no, fyear
    ) AS D
    GROUP BY customer_no, grp

    order by customer_no, sort

    drop table #work1

Reply
  • $organization

    i found my original code which is easier to use and follow. the solution is called Gaps and Islands, per the google machine.

    select
    ROW_NUMBER() over(partition by customer_no order by cont_amt desc) as sort_largest,
    ROW_NUMBER() over(partition by customer_no order by cont_dt desc) as sort_newest,
    ROW_NUMBER() over(partition by customer_no order by cont_dt asc) as sort_oldest,
    customer_no,
    cont_amt,
    cont_dt,
    isnull(fyear, case when DATEPART(MM,cont_dt) > 6 then cast(DATEPART(YYYY,cont_dt) as int) + 1 else cast(DATEPART(YYYY,cont_dt) as int) end) as fyear
    into #work1
    from
    T_CONTRIBUTION a
    join T_CAMPAIGN b on a.campaign_no = b.campaign_no
    where
    cont_amt > 0
    --order by
    --customer_no

    select customer_no, fyear from #work1 order by customer_no, fyear

    SELECT
    customer_no,
    fyear,
    fyear - dense_rank() OVER(partition by customer_no ORDER BY customer_no, fyear) AS grp
    FROM #work1
    order by customer_no, fyear


    SELECT
    ROW_NUMBER () over(partition by customer_no order by max(fyear) - min(fyear) +1 desc) as Sort,
    customer_no,
    MIN(fyear) AS startYr,
    MAX(fyear) AS endYr,
    max(fyear) - min(fyear) +1 as consYrs

    FROM (
    SELECT
    customer_no,
    fyear,
    fyear - dense_rank() OVER(partition by customer_no ORDER BY customer_no, fyear) AS grp
    FROM
    #work1
    --order by customer_no, fyear
    ) AS D
    GROUP BY customer_no, grp

    order by customer_no, sort

    drop table #work1

Children