Microsoft T-SQL Counting Consecutive Records

Question

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

Firstly, I am sorry but new to this system and having trouble figuring out how to work the interface and post properly.

2010-05-13 ---------------------------------------------------------------------------
Joel, Thank you so much for your response below! I need it for a key production job that was running about 60 minutes.
Now the job runs in 2 minutes!!

Yes, there was 1 condition in my case that I needed to address. My source always had only record for those that had been bad recently so that was not a problem for me. I did however have to handle records where they were never good, and did that with a left join to add back in the records and gave them a date so the counting would work for all.

Thanks again for your help. It was opened my mind some more to SET based logic and how to approach it and was a HUGE benefit to my production job.

Answer 1

The basic solution here is to first build a set that contains the name of each person and the value of the last day on which that person was good. Then join this set to the original table and group by name to find the count of days > the last good day for each person. You can build the set in either a CTE, a view, or an uncorrelated derived table (sub query) — any of those will work. My example below uses a CTE.

Note that while the concept is sound, this specific example might not return exactly what you want. Your actual needs here depend on what you want to happen for those who have not been good ever and for those who have not been bad recently (ie, you might need a left join to show users who were good yesterday). But this should get you started:

WITH LastGoodDays AS
(
  SELECT MAX([date]) as [date], name
  FROM [table]
  WHERE Points > 0
  GROUP BY name
)
SELECT t.name, count(*) As ConsecutiveBadDays
FROM [table] t
INNER JOIN LastGoodDays lgd ON lgd.name = t.name AND t.[date] > lgd.[date]
group by t.name