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
