Thanks once again for an excellent reply. The table worked well with the real data. However there's some weird hiccups with the query. If i execute the query multiple times, it goes into for ever executing mode... i.e cant complete the execution.

Here's the slightly modified query:

WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(N)),

CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),

CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),

CTE4(N) AS (SELECT 1 FROM CTE3 x CROSS JOIN CTE3 y),

CTE5(N) AS (SELECT 1 FROM CTE4 x CROSS JOIN CTE4 y),

CTE6(N) AS (SELECT 0 UNION ALL

SELECT TOP 97199 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

FROM CTE5),

TALLY(N) AS (SELECT DATEADD(month, N, 0)

FROM CTE6)

SELECT a.N AS recorddate, ISNULL(b.myValue,0) AS myValue

FROM TALLY a

OUTER APPLY (SELECT DATEADD(month, DATEDIFF(month, 0, f.recorddate), 0), SUM(e.numvalue)

FROM admin_tasks as e INNER JOIN admin_tasks as f ON e.id=f.numvalue

WHERE DATEADD(month, DATEDIFF(month, 0, f.recorddate), 0) = a.N AND f.actionid=5 AND f.numtype=1

GROUP BY DATEADD(month, DATEDIFF(month, 0, f.recorddate), 0)

) b(recorddate,myValue)

CROSS APPLY (SELECT MIN(DATEADD(month, DATEDIFF(month, 0, h.recorddate), 0)),

MAX(DATEADD(month, DATEDIFF(month, 0, h.recorddate), 0))

FROM admin_tasks as g INNER JOIN admin_tasks as h ON g.id=h.numvalue WHERE h.actionid=5 AND h.numtype=1) d(minDate,maxDate)

WHERE a.N >= d.minDate AND a.N <= d.maxDate

ORDER BY recorddate ASC

And here's the statistics IO & time

(11 row(s) affected)

Table 'admin_tasks'. Scan count 17, logical reads 8099, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 11, logical reads 28546, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 187 ms, elapsed time = 257 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

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. 🙂