memymasta (12/3/2012)
A followup question: if i only need the years span of 2000-2050, how do i select them instead of
SELECT TOP 97199 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM CTE5Thanks
Well, there are 611 months between 2000-01-01 and 2050-12-01, so you'd want to change the code to something like this: -
CTE6(N) AS (SELECT 0 UNION ALL
SELECT TOP 611 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE5),
TALLY(N) AS (SELECT DATEADD(month, N, CAST('2000' AS DATETIME))
FROM CTE6)
Note the change to the "tally" section, which starts the calendar at 2000-01-01 instead of the previous version which started at 1900-01-01.
memymasta (12/3/2012)
Any thought on why the query stagnates?UPDATE: Checked some variables and getting 97k rows was a slight overkill. Changing it to 2050 rows made a big difference, and no more hickups. 🙂
Without seeing an actual execution plan, no. I'd imagine it'll be an indexing issue. You could also try changing the CTE to a physical table of all of the months, which when properly indexes may give some performance boost.