SQL Kiwi (1/11/2012)

---

drew.allen (1/11/2012)

---

Bob Cullen-434885 (1/10/2012)

---

I started to look at an alternative approach, using a recursive CTE to generate a continuous list of dates that covered the report range

Jeff Moden has an article that shows how badly recursive CTEs can perform for certain types of tasks and it sounds like yours may fall into that category. Check out his article Hidden RBAR: Counting with Recursive CTE's[/url]

It's pretty tough to imagine a list of dates long enough to make any practical difference regarding performance here. Before anyone says it, yes I know that it often makes sense to code for the 10-million row case, but that would take us into the year 4749 here. A mere 4000 rows (150ms from Jeff's article graph) is more than enough to cover a ten-year period.

I congratulate Bob for finding my error and pursuing an alternative line of enquiry. My concern is that we shouldn't be too keen in promoting The One True Way to generate numbers such that we end up stifling innovation and exploration.