SQL code for consecutive years as donor

here are two pieces of code that are part of a larger set of CTEs. first is a table of contributions. this will need to be modified to fit your needs. second is consecutive giving. if you want the whole set of code i'm happy to share. 

1.

conts as
(
select TOP 100 PERCENT
*
from
(
select
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_amt desc) as sort_largest,
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_dt desc) as sort_newest,
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_dt asc) as sort_oldest,
a.customer_no,
cust.group_customer_no,
a.cont_amt,
a.cont_dt,
a.ref_no,
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,
a.channel,
a.campaign_no,
b.description as cmp_desc,
case
when a.campaign_no in (11,358,431,695) then 'Biltmore'
when a.campaign_no in (707) then 'Stage_II'
else ''
end as capital,
b.camp_type,
a.billing_type, -- 4 = CC recuring
a.media_type, -- 11 = unsolicited
'' as creditee_type,
d.cust_memb_no
from
T_CONTRIBUTION a
join cust on a.customer_no = cust.customer_no
join T_CAMPAIGN b (nolock) on a.campaign_no = b.campaign_no
join TX_APPEAL_MEDIA_TYPE c (nolock) on a.source_no = c.source_no
left outer join TX_CONT_MEMB d on a.ref_no = d.cont_ref_no

union all -- soft credits (conts are double counted so totals must seperate giving and creditee types into columns)

select TOP 100 PERCENT
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_amt desc) as sort_largest,
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_dt desc) as sort_newest,
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_dt asc) as sort_oldest,
cred.creditee_no as customer_no,
cust.group_customer_no,
a.cont_amt,
a.cont_dt,
a.ref_no,
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,
a.channel,
a.campaign_no,
b.description as cmp_desc,
case
when a.campaign_no in (11,358,431,695) then 'Biltmore'
when a.campaign_no in (707) then 'Stage_II'
else ''
end as capital,
b.camp_type,
a.billing_type, -- 4 = CC recuring
a.media_type, -- 11 = unsolicited
cred_tp.descriptiuon as creditee_type, -- cred.creditee_type
d.cust_memb_no
from
T_CONTRIBUTION a
join T_CREDITEE (nolock) cred on a.ref_no = cred.ref_no
join TR_CREDITEE_TYPE cred_tp on cred.creditee_type = cred_tp.id
join cust on cred.creditee_no = cust.customer_no
join T_CAMPAIGN b (nolock) on a.campaign_no = b.campaign_no
join TX_APPEAL_MEDIA_TYPE c (nolock) on a.source_no = c.source_no
left outer join TX_CONT_MEMB d on a.ref_no = d.cont_ref_no
)a
order by a.group_customer_no

),

2.

cons_Gvng as
(
select TOP 100 PERCENT
ROW_NUMBER () over(partition by group_customer_no order by max(fyear) - min(fyear) +1 desc) as Sort,
group_customer_no,
MIN(fyear) AS startYr,
MAX(fyear) AS endYr,
max(fyear) - min(fyear) +1 as Consecutive
FROM (
SELECT group_customer_no, fyear, fyear - dense_rank() OVER(partition by group_customer_no ORDER BY customer_no, fyear) AS grp
FROM conts
--order by customer_no, fyear
) AS D
GROUP BY group_customer_no, grp
order by group_customer_no
),

here are two pieces of code that are part of a larger set of CTEs. first is a table of contributions. this will need to be modified to fit your needs. second is consecutive giving. if you want the whole set of code i'm happy to share. 

1.

conts as
(
select TOP 100 PERCENT
*
from
(
select
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_amt desc) as sort_largest,
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_dt desc) as sort_newest,
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_dt asc) as sort_oldest,
a.customer_no,
cust.group_customer_no,
a.cont_amt,
a.cont_dt,
a.ref_no,
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,
a.channel,
a.campaign_no,
b.description as cmp_desc,
case
when a.campaign_no in (11,358,431,695) then 'Biltmore'
when a.campaign_no in (707) then 'Stage_II'
else ''
end as capital,
b.camp_type,
a.billing_type, -- 4 = CC recuring
a.media_type, -- 11 = unsolicited
'' as creditee_type,
d.cust_memb_no
from
T_CONTRIBUTION a
join cust on a.customer_no = cust.customer_no
join T_CAMPAIGN b (nolock) on a.campaign_no = b.campaign_no
join TX_APPEAL_MEDIA_TYPE c (nolock) on a.source_no = c.source_no
left outer join TX_CONT_MEMB d on a.ref_no = d.cont_ref_no

union all -- soft credits (conts are double counted so totals must seperate giving and creditee types into columns)

select TOP 100 PERCENT
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_amt desc) as sort_largest,
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_dt desc) as sort_newest,
ROW_NUMBER() over(partition by cust.group_customer_no order by a.cont_dt asc) as sort_oldest,
cred.creditee_no as customer_no,
cust.group_customer_no,
a.cont_amt,
a.cont_dt,
a.ref_no,
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,
a.channel,
a.campaign_no,
b.description as cmp_desc,
case
when a.campaign_no in (11,358,431,695) then 'Biltmore'
when a.campaign_no in (707) then 'Stage_II'
else ''
end as capital,
b.camp_type,
a.billing_type, -- 4 = CC recuring
a.media_type, -- 11 = unsolicited
cred_tp.descriptiuon as creditee_type, -- cred.creditee_type
d.cust_memb_no
from
T_CONTRIBUTION a
join T_CREDITEE (nolock) cred on a.ref_no = cred.ref_no
join TR_CREDITEE_TYPE cred_tp on cred.creditee_type = cred_tp.id
join cust on cred.creditee_no = cust.customer_no
join T_CAMPAIGN b (nolock) on a.campaign_no = b.campaign_no
join TX_APPEAL_MEDIA_TYPE c (nolock) on a.source_no = c.source_no
left outer join TX_CONT_MEMB d on a.ref_no = d.cont_ref_no
)a
order by a.group_customer_no

),

2.

cons_Gvng as
(
select TOP 100 PERCENT
ROW_NUMBER () over(partition by group_customer_no order by max(fyear) - min(fyear) +1 desc) as Sort,
group_customer_no,
MIN(fyear) AS startYr,
MAX(fyear) AS endYr,
max(fyear) - min(fyear) +1 as Consecutive
FROM (
SELECT group_customer_no, fyear, fyear - dense_rank() OVER(partition by group_customer_no ORDER BY customer_no, fyear) AS grp
FROM conts
--order by customer_no, fyear
) AS D
GROUP BY group_customer_no, grp
order by group_customer_no
),