Count of Consecutive Decending Values Up to the First Gap


https://social.msdn.microsoft.com/Forums/en-US/e083c5fa-c82b-47bc-bc9e-657a58344986/count-of-consecutive-decending-values-up-to-the-first-gap?forum=transactsql

  
 
   create table test (customer_no int,	membership_year int)
Insert into test values
(1,2016),(1,2015),(1,2014),(1,2008),(1,2007)
,(2,2012),(2,2011),(2,2010),(2,2007),(2,2006),
(3,2017),(3,2016),(3,2015),(3,2014),(3,2013),(3,2012)
 
 
  ;with mycte as (
select  customer_no, membership_year
, DATEADD(year, - ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY membership_year), datefromparts(membership_year,1,1)) AS grp
  from test
)
,mycte1 as (
select customer_no,  membership_year, grp , max(membership_year) Over (PARTITION BY customer_no) maxYear, Year(getdate()) curYear
, DENSE_RANK() OVER(PARTITION BY customer_no  ORDER BY grp desc) AS Ranking 
from mycte
)
Select customer_no, ISNULL(SUM(case when maxYear>=curYear and Ranking=1 then 1 else null end) -1 ,0)  as  cnt  
From mycte1
Group by customer_no
order by customer_no


/*
Customer 1 would have 2 consecutive years
Customer 2 would have 0 (zero) because they are no longer a member as of 2012
Customer 3 would have 5 consecutive years. 
*/

Drop table test


  
   
 
 

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s