SQL code for consecutive years as donor

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.

My pleasure!  Thanks for letting me know.