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.