Microsoft T-SQL sequential record counting

Question

Problem: Starting from the current day per person, count the number of consecutive days during which each person received 0 points for the fact that they were 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 at 5/7, but good the day before. So lately, Jane has been bad for 1 day in a row. John was bad at 5/7, again at 5/6, 5/5 and 5/4. He was good at 5/3. So John has been bad for the last 4 days in a row.

Code for creating 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'm sorry, but new to this system and don’t know how to work with the interface and place it correctly.

2010-05-13 ------------------------------------------ --- ------------------------------
Joel, thanks so much for your answer below! I need this for key production work, which lasted about 60 minutes.
Now work is done in 2 minutes!

Yes, in my case there was 1 condition that I needed to apply to. My source always had only records for those who were recently ill, so this is not a problem for me. However, I had to process the records where they were never good, and I did it with the left join to add back to the records and gave them a date, so counting will work for everyone.

Thanks again for your help. This once again opened my eyes to the logic based on SET, and how to approach it, and a HUGE advantage for my production work.

Answer 1

The main solution here is to first create a set containing the name of each person and the meaning of the last day when that person was good. Then attach this set to the source table and group by name to find the number of days> last good day for each person. You can create a set in a CTE, view, or uncorrelated subquery - any of them will work. My example below uses CTE.

Please note that although the concept is sound, this particular example may not return exactly what you want. Your actual needs here depend on what you want to do for those who have not been good ever and those who have not been bad recently (i.e. you may need a left connection to show users who were good yesterday). But you need to start:

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