SELECT
	CAST(YEAR(t.dateCreated) as nvarchar) + '-' + CAST(RIGHT('0' + RTRIM(MONTH(t.dateCreated)), 2) as nvarchar) as colName, -- needs to be 'YYYY-MM' so that ASP.NET can sort as a string 
	COUNT(*) AS totals
FROM
	@tbl_results t
GROUP BY
	DATENAME(MONTH, t.dateCreated) + ' ' + CAST(YEAR(t.dateCreated) AS nvarchar), MONTH(t.dateCreated), YEAR(t.dateCreated)
ORDER BY
	1

colName	totals
--------------
2010-08	1
2010-10	95
2010-11	552
2011-01	153
2011-02	278

SELECT
	convert(char(7),t.dateCreated,120) as colName, -- needs to be 'YYYY-MM' so that ASP.NET can sort as a string 
	COUNT(*) AS totals,
	sum(1) over () as aggregate
FROM
	@tbl_results t
GROUP BY
	convert(char(7),t.dateCreated,120)
ORDER BY
	1

colName	totals	aggregate
2010-08	1	22
2010-10	95	22
2010-11	552	22
2011-01	153	22
2011-02	278	22
2011-03	4	22
2011-04	6	22

colName	totals	aggregate
2010-08	1	1
2010-10	95	96
2010-11	552	648
2011-01	153	801
2011-02	278	1079

;WITH cte AS
(
	SELECT
		DATEADD(mm,DATEDIFF(mm,0,dateCreated),0) AS Created,
		COUNT(*) AS totals
	FROM
		tbl_results
	GROUP BY
		DATEADD(mm,DATEDIFF(mm,0,dateCreated),0)
)
SELECT
	CONVERT(CHAR(7),a.Created,120) AS colName,
	a.totals,
	b.CumulativeTotal
FROM
	cte a
	CROSS APPLY
	(
		SELECT SUM(totals) AS CumulativeTotal
		FROM cte c
		WHERE c.Created <= a.Created
	) b;

quote:

---

Originally posted by R

Hi sunitabeck

That does work thank you, but I have concerns about your statement regarding it not being very efficient. Is there an efficient way in SQL 2005??

---

;WITH cte AS
	(
	SELECT
		CAST(YEAR(t.dateCreated) as nvarchar) + '-' + CAST(RIGHT('0' + RTRIM(MONTH(t.dateCreated)), 2) as nvarchar) as colName,
		COUNT(*) AS totals
	FROM
		@tbl_results t
	GROUP BY
		DATENAME(MONTH, t.dateCreated) + ' ' + CAST(YEAR(t.dateCreated) AS nvarchar), MONTH(t.dateCreated), YEAR(t.dateCreated)
	)
SELECT
	*
FROM
	cte c1 CROSS APPLY
	(
	SELECT 
		SUM(totals) as ct
	FROM
		cte c2
	WHERE
		c2.colName <= c1.colName
	) b
ORDER BY
	c1.colName