There is a particular problem, let's' say, we just want to find out who sold more then 2 each month in at least two consecutive months and when.

For the above sample, below recursion would work. Also, recursion will stop immediately when it reaches the first qualified date.

with m2_cte_f (name,saledate,quantity,ind) as (

select s.*, 0 as ind

from #sales s

where s.saledate='2012-01-01'

union all

select s.*, case when s.quantity > 2 and sc.quantity > 2 then 1 else 0 end as ind

from #sales s

inner join m2_cte_f sc

on (s.saledate = dateadd(month,1,sc.saledate) and s.name=sc.name)

where sc.ind = 0

)

select * from m2_cte_f where ind=1