Jeff Moden (11/19/2011)
First, nicely done there Cold Coffee. I came up with an almost identical solution and saw yours when I was getting ready to post it. Your solution uses MAX() OVER (brilliant idea, BTW) which I believe is going to be more effecient than the method I'm using to calculate a pseudo-group number because I use an extra ROW_NUMBER() OVER to do it. That's provided that the RecordSeq column always reflects the correct order by Account and Date. If it doesn't, then there will be a problem. Replace the 8/6 date for the second row of "Jim" with 9/6 and see what I mean.
Sorry to revive an old thread, but wanted to say "Thanks"
I was doing something similar, with crazy loop processing running for hours and hours, and your code ran in about 15 seconds !