Problem: From the most current day per person, count the number of consecutive days that each person has received 0 points for being good.

Sample data to work from :

Date       Name Points
2010-05-07 Jane 0  
2010-05-06 Jane 1  
2010-05-07 John 0  
2010-05-06 John 0  
2010-05-05 John 0  
2010-05-04 John 0  
2010-05-03 John 1  
2010-05-02 John 1  
2010-05-01 John 0

Expected answer:

Jane was bad on 5/7 but good the day before that. So Jane was only bad 1 day in a row most recently. John was bad on 5/7, again on 5/6, 5/5 and 5/4. He was good on 5/3. So John was bad the last 4 days in a row.

Code to create sample data:

IF OBJECT_ID('tempdb..#z') IS NOT NULL BEGIN DROP TABLE #z END
select getdate() as Date,'John' as Name,0 as Points into #z 
insert into #z values(getdate()-1,'John',0)
insert into #z values(getdate()-2,'John',0)
insert into #z values(getdate()-3,'John',0)
insert into #z values(getdate()-4,'John',1)
insert into #z values(getdate(),'Jane',0)
insert into #z values(getdate()-1,'Jane',1)
select * from #z order by name,date desc