nathan 7372 (2/26/2010)
Well after 1 hour and 11 minutes it reached the maximum recursion of 32767 and terminated. I'm going to try and figure out what exactly this query is doing because it works beautifully when there is only one account but when you add more than one account it breaks. I'm going to edit the sample data to include another account. Sorry about that.
I was afraid of that when I saw the recursive CTE. They actually tend to be a bit slower than even a cursor. How many rows in total are you processing?
Also, what do you mean by "Refund"? Are you talking about the Amount column?
If all goes well, the example I'm writing should give you what you want at a rate of about a million rows every 7 seconds or so. Nope... that's not a misprint.
--Jeff Moden
Change is inevitable... Change for the better is not.