How to Count consecutive dates

Question

I'm look for a query in T-Sql to count the number of consecutive dates, backwards where the pop is the same, starting at the latest date and stopping when there is a gap in the date.

This is an example of the data:

Name    village Population  Date
Rob     village1    432     01/07/2013
Rob     village2    432     30/06/2013
Rob     village3    432     29/06/2013
Rob     village3    432     28/06/2013
Rob     village3    432     27/06/2013
Rob     village3    430     26/06/2013
Rob     village3    430     25/06/2013
Rob     village3    430     24/06/2013
Rob     village3    430     23/06/2013
Rob     village3    425     22/06/2013
Rob     village3    422     21/06/2013
Rob     village3    422     20/06/2013
Rob     village3    411     19/06/2013
Harry   Village1    123     01/07/2013
Harry   Village2    123     30/06/2013
Harry   Village3    122     29/06/2013
Pat     Village1    123     01/07/2013
Pat     Village2    123     30/06/2013
Pat     Village3    123     29/06/2013
Pat     Village4    100     20/06/2013
Tom     Village1    123     01/07/2013
Tom     Village2    123     30/06/2013
Tom     Village3    123     29/06/2013
Tom     Village4    123     28/06/2013

I would expect to get the following results:

Rob 5
Harry 2
Pat 3
Tom 3

The data should be more complex, but there will be 1000's of rows, 100's per person and groups of pop with consecutive dates, but i only want the first set of consecutive dates with the same pop, from the latest downwards.

Answer 1

with dd as 
(
   select distinct * from table
);
    select name, max(count) + 1
    from 
    (
       select t1.name, t1.village, t1.pop, count(*) as count
       from dd t1 
       join dd t2 
         on t2.village = t1.village
        and t2.pop  = t1.pop 
        and t2.pop  = t1.pop 
        and t2.date = dateadd(day,-1,t1.date)
       group by t1.name, t1.village, t1.pop
    )  dates
    group by name