SQL code for consecutive years as donor

That's a tricky one.  

1998

1999

2000

2001

2003

2004

2005

2008

2017

2018

What number are you looking for from that sequence?

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
),

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

That is a tricky philosophical question!  I say 4, as the longest contiguous stretch, but I don't know if that necessarily is the answer that folks who analyze donor activity will find useful.

I can't seem to reply to Mendy's 2 posts this morning, but thank you so much!

try replying here?

Thanks for sharing these, Mendy, I look forward to messing with them!

I just did this the other day

BWG

USE [impresario]

GO

/****** Object:  StoredProcedure [dbo].[LP_UP_LT_CONT_YEARS]    Script Date: 3/12/2019 6:10:26 PM ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[LP_UP_LT_CONT_YEARS] as

/***************************************************************************************************

BWG 2/28/2019

update LT_CONT_YEARS which holds the number of consecutive years a donor has been giving

select

years, count(*)

from #sub_years where fyear in (2018,2019)

group by years

order by years

SELECT DISTINCT CUSTOMER_NO

FROM LT_CONT_YEARS WHERE YEARS >= 20 AND fyear >= 2019

***************************************************************************************************/

create table #sub_years (

  customer_no int null,

  fyear int null,

  years int null)

declare cur_years cursor for

select distinct

a.customer_no,

b.fyear

from t_contribution a (nolock)

join t_campaign b (nolock) on a.campaign_no = b.campaign_no

--where b.description like '%memb%'

order by a.customer_no, b.fyear

declare

  @customer_no int,

  @current_customer_no int,

  @current_fyear int,

  @fyear int,

  @years int

open cur_years

fetch cur_years into

  @customer_no,

  @fyear

set @years = 1

set @current_customer_no = @customer_no

set @current_fyear = @fyear

while @@fetch_status = 0

begin

       insert into #sub_years (customer_no, fyear, years)

       values (@customer_no, @fyear, @years)

       set @current_customer_no = @customer_no

       set @current_fyear = @fyear

       fetch cur_years into @customer_no, @fyear

       set @years = case when @current_customer_no = @customer_no and @current_fyear = @fyear - 1 then  @years + 1 else 1 end

--this resets a patrons'cumalitve years if

end

close cur_years

deallocate cur_years

select customer_no, max(fyear) as max_fyear

into #sub_year_max

from #sub_years

group by customer_no

delete from LT_CONT_YEARS

insert LT_CONT_YEARS (customer_no, years, fyear)

select

a.customer_no,

a.years,

a.fyear

from

#sub_years a

join #sub_year_max b on a.customer_no = b.customer_no and a.fyear = b.max_fyear

GO

GRANT EXECUTE ON [dbo].[LP_UP_LT_CONT_YEARS] TO [ImpUsers] AS [dbo]

GO

GRANT EXECUTE ON [dbo].[LP_UP_LT_CONT_YEARS] TO [tessitura_app] AS [dbo]

GO

Thanks, Brian!  It is good to have smart friends sharing code...

My pleasure!  Thanks for letting me know.

I haven't optimized this query because I only use it from time to time and usually on smaller groups. So I would suggest putting in another cte with a window/partition function to get rid of dupes. Or use a group by clause to get rid of the dupes. Also, this query is only looking for the max amount of consecutive in a set amount of years.

But overall the query works well for my needs:

with cte1 as
(select *, year(cont_dt) as contyear
from T_CONTRIBUTION x
)
select
a.customer_no, fname, lname, ayear, byear, yeardiff, ranki
from
(
select
a.customer_no,
a.contyear as ayear,
b.contyear as byear,
(b.contyear - a.contyear)+1 yeardiff,
dense_rank() over (partition by a.customer_no order by (b.contyear - a.contyear) desc) ranki
from
cte1 a
join cte1 b on a.customer_no = b.customer_no
and b.contyear > a.contyear
where
b.contyear - a.contyear =
(select count(distinct contyear)-1
from cte1 a1
where a.customer_no = a1.customer_no
and a1.contyear between a.contyear and b.contyear and cont_dt >= '2013-01-01' )
) a
inner join T_CUSTOMER m
on a.customer_no = m.customer_no
inner join T_CONTRIBUTION k
on a.customer_no = k.customer_no
inner join
(SELECT a1.customer_no
FROM t_list_contents AS a1
WHERE a1.list_no IN (62259)) AS e
ON e.customer_no = a.customer_no
where ranki = 1 and cust_type in (1,7) and cont_dt <= '2018-12-31'
order by a.customer_no