How to make a query that just counts an unbroken stream?

  

GAPS and ISLANDS

This solution uses the search principle 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;

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

  

CTE RECURSIVA

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

-- 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;

To test:

-- 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 person 1213, information about the year 2015 is purposely not given, to demonstrate that the code also treats a break in sequence (ie no line).

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;