How to make a query that brings only the count of an uninterrupted sequence?

Question

I want the counting of uninterrupted values of the current year backwards, until the first occurs NULL.

Example:

Several people make donations to an NGO annually and this is stored in a database, would like to know how many years a person made donations uninterruptedly, ie, how many consecutive years were made donations from beginning of the current year.

Donations table:

Pessoa_id | Valor | Ano  
9999      | 100   | 2009  
9999      | 100   | 2010  
9999      | 100   | 2011  
9999      | NULL  | 2012  
9999      | 100   | 2013  
9999      | NULL  | 2014  
9999      | 100   | 2015  
9999      | 100   | 2016  
9999      | 100   | 2017  

The result should be 3, as counting the current year(2017) plus the two years he donated (2016 e 2015).

Answer 1

GAPS and ISLANDS

Here is the solution that uses the principle of search for gaps and Islands.

-- código #1 v4
with 
C as (
SELECT Pessoa_Id, Ano, Valor,
       ilha= (Ano - DENSE_RANK() OVER(partition by Pessoa_Id order by Ano)) 
  from Doação
  where Valor is not null
)
SELECT Pessoa_Id, 
       count(*) as Ininterrupto,
       min(Valor) as [Menor valor],
       max(Valor) as [Maior valor],
       avg(Valor) as Média,
       sum(Valor) as Total  
  from C
  group by Pessoa_Id, ilha
  having max(Ano) = 2017;

Adapted code of the article Solving Gaps and Islands with Enhanced Window Functions. If the SQL Server version is 2012, or newer, you can use the new window functions, as stated in the article.

---

CTE RECURSIVE

Here is another solution, based on CTE recursive that starts reading by all lines of the year 2017 that have value informed and continues reading in sequence inverse order, until find no value (NULL) or break in the column of year.

-- código #2
with bloco2017 as (
SELECT Pessoa_id, Ano, Valor
  from Doação
  where Ano = 2017 
        and Valor is not null

union all 
SELECT T1.Pessoa_id, T1.Ano, T1.Valor
  from Doação as T1
       inner join bloco2017 as T2 on T1.Pessoa_Id = T2.Pessoa_Id
                                     and T1.Ano = (T2.Ano -1)
  where T1.Valor is not null
)
SELECT Pessoa_Id, 
       count(*) as Ininterrupto,
       min(Valor) as [Menor valor],
       max(Valor) as [Maior valor],
       avg(Valor) as Média,
       sum(Valor) as Total
  from bloco2017
  group by Pessoa_Id;

---

For testing:

-- código #3 v2
CREATE TABLE Doação (
   Pessoa_id int,
   Valor money,
   Ano smallint
);

INSERT into Doação values 
 (1212, 80, 2009),
 (1212, 90, 2010),
 (1212, 100, 2011),
 (1212, NULL, 2012),
 (1212, 120, 2013),
 (1212, NULL, 2014),
 (1212, NULL, 2015),
 (1212, 150, 2016),
 (1212, 160, 2017);

INSERT into Doação values 
 (1213, 300, 2009),
 (1213, 200, 2010),
 (1213, 100, 2011),
 (1213, 50, 2012),
 (1213, NULL, 2013),
 (1213, 20, 2014),
 (1213, 800, 2016),
 (1213, 100, 2017); 

For the person 1213 purposely does not contain information about the year 2015, to demonstrate that the code also deals with breaking in sequence (i.e., absence of line).

Answer 2

I think the query would look something like this, working with variables:

SET @total = 0;
SELECT 
    MAX(total)
FROM(
SELECT
    @total:=@total+1 AS total,
    IFNULL(valor, @total:=0) as aux,
    valor,
    ano
FROM doacao
HAVING aux != 0) AS aux;