[Solved] Sql query to Count Total Consecutive Years from latest year

by 


select ID,
   -- returns a sequence without gaps for consecutive years
   first_value(year) over (partition by ID order by year desc) - year +1 as x, 
   -- returns a sequence without gaps
   row_number() over (partition by ID order by year desc) as rn
from Temp

e.g. for ID=1:

1   2016    1   1
1   2015    2   2
1   2012    5   3
1   2011    6   4
1   2010    7   5

As long as there’s no gap, both sequences increase the same.

Now check for equal sequences and count the rows:

with cte as 
 (
   select ID,
      -- returns a sequence without gaps for consecutive years
      first_value(year) over (partition by ID order by year desc) - year + 1 as x, 
      -- returns a sequence without gaps
      row_number() over (partition by ID order by year desc) as rn
   from Temp

 ) 
select ID, count(*)
from cte
where x = rn  -- no gap
group by ID

Edit:

Based on your year zero comment:

with cte as 
 (
   select ID, year,
      -- returns a sequence without gaps for consecutive years
      first_value(year) over (partition by ID order by year desc) - year + 1 as x, 
      -- returns a sequence without gaps
      row_number() over (partition by ID order by year desc) as rn
   from Temp

 ) 
select ID, 
   -- remove the year zero from counting
   sum(case when year <> 0 then 1 else 0 end)
from cte
where x = rn
group by ID

5

solved Sql query to Count Total Consecutive Years from latest year