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)

colName	totals
2011-10	524
2011-11	544
2011-12	234
2012-01	102
2012-03	678

colName	totals
2011-10	524
2011-11	544
2011-12	234
2012-01	102
2012-02 0
2012-03	678

;WITH cteMonths(FromMonth, ToMonth)
AS (
	SELECT	MIN(DateCreated),
		MAX(DateCreated)
	FROM	@Tbl_Results

	UNION ALL

	SELECT	DATEADD(MONTH, 1, FromMonth),
		ToMonth
	FROM	cteMonths
	WHERE	DATEDIFF(MONTH, FromMonth, ToMonth) >= 0
)
SELECT		CONVERT(CHAR(7), m.FromMonth, 120) AS ColName,
		COUNT(t.DateCreated) AS Totals
FROM		cteMonths AS m
LEFT JOIN	@Tbl_Results AS t
GROUP BY	CONVERT(CHAR(7), m.FromMonth, 120)
ORDER BY	CONVERT(CHAR(7), m.FromMonth, 120)

quote:

---

You need a table with all the wanted dates then left join your @tbl_results and each time a value is NULL because there is nothing to join you can display zero as totals.

---

quote:

---

;WITH cteMonths(FromMonth, ToMonth)
AS (
SELECT MIN(DateCreated),
MAX(DateCreated)
FROM @Tbl_Results

UNION ALL

SELECT DATEADD(MONTH, 1, FromMonth),
ToMonth
FROM cteMonths
WHERE DATEDIFF(MONTH, FromMonth, ToMonth) >= 0
)
SELECT CONVERT(CHAR(7), m.FromMonth, 120) AS ColName,
COUNT(t.DateCreated) AS Totals
FROM cteMonths AS m
LEFT JOIN @Tbl_Results AS t
GROUP BY CONVERT(CHAR(7), m.FromMonth, 120)
ORDER BY CONVERT(CHAR(7), m.FromMonth, 120)

---

quote:

---

Originally posted by SwePeso

;WITH cteMonths(FromMonth, ToMonth)
AS (
	SELECT	MIN(DateCreated),
		MAX(DateCreated)
	FROM	@Tbl_Results

	UNION ALL

	SELECT	DATEADD(MONTH, 1, FromMonth),
		ToMonth
	FROM	cteMonths
	WHERE	DATEDIFF(MONTH, FromMonth, ToMonth) >= 0
)
SELECT		CONVERT(CHAR(7), m.FromMonth, 120) AS ColName,
		COUNT(t.DateCreated) AS Totals
FROM		cteMonths AS m
LEFT JOIN	@Tbl_Results AS t ON dateadd(d,datediff(d,0,t.DateCreated),0) = dateadd(d,datediff(d,0,m.FromMonth),0)
GROUP BY	CONVERT(CHAR(7), m.FromMonth, 120)
ORDER BY	CONVERT(CHAR(7), m.FromMonth, 120)

---

N 56°04'39.26"
E 12°55'05.63"

---

;WITH cteMonths(FromMonth, ToMonth, Display)
AS (
	SELECT	MIN(DateCreated),
		MAX(DateCreated),
		CONVERT(CHAR(7), MIN(DateCreated), 120)
	FROM	@Tbl_Results

	UNION ALL

	SELECT	DATEADD(MONTH, 1, FromMonth),
		ToMonth,
		CONVERT(CHAR(7), DATEADD(MONTH, 1, FromMonth), 120)
	FROM	cteMonths
	WHERE	DATEDIFF(MONTH, FromMonth, ToMonth) >= 0
)
SELECT		m.Display AS ColName,
		ISNULL(t.Items, 0) AS Totals
FROM		cteMonths AS m
LEFT JOIN	(
			SELECT		CONVERT(CHAR(7), DateCreated, 120) AS Display,
					COUNT(*) AS Items
			FROM		@Tbl_Results
			GROUP BY	CONVERT(CHAR(7), DateCreated, 120)
		) AS t ON t.Display = m.Display
ORDER BY	m.Display

;WITH cteMonths(theMonth, stopMonth)
AS (
	SELECT	DATEDIFF(MONTH, '19000101', MIN(DateCreated)) AS theMonth,
		DATEDIFF(MONTH, '19000101', MAX(DateCreated)) AS stopMonth
	FROM	@Tbl_Results

	UNION ALL

	SELECT	theMonth + 1 AS theMonth,
		stopMonth
	FROM	cteMonths
	WHERE	theMonth < stopMonth
)
SELECT		CONVERT(CHAR(7), DATEADD(MONTH, m.theMonth, '19000101'), 120) AS ColName,
		ISNULL(t.Items, 0) AS Totals
FROM		cteMonths AS m
LEFT JOIN	(
			SELECT		DATEDIFF(MONTH, '19000101', DateCreated) AS theMonth,
					COUNT(*) AS Items
			FROM		@Tbl_Results
			GROUP BY	DATEDIFF(MONTH, '19000101', DateCreated)
		) AS t ON t.theMonth = m.theMonth
ORDER BY	m.theMonth

colName totals runningTotals
2006-04	2      2
2006-05	0      2
2006-06	0      2
2006-07	0      2
2006-08	5      7
2006-09	0      7
2006-10	3      10

SET NOCOUNT ON;

CREATE TABLE	#Data
		(
			colName CHAR(7) NOT NULL,
			theMonth INT NOT NULL,
			totals INT NOT NULL
		);

CREATE UNIQUE INDEX UX_Data ON #Data (theMonth) WITH (IGNORE_DUP_KEY = ON);

WITH cteSource(theMonth, totals)
AS (
	SELECT		DATEDIFF(MONTH, '19000101', DateCreated) AS theMonth,
			COUNT(*) AS totals
	FROM		@Tbl_Results
	GROUP BY	DATEDIFF(MONTH, '19000101', DateCreated)
)
INSERT	#Data
	(
		colName,
		theMonth,
		totals
	)
SELECT	CONVERT(CHAR(7), DATEADD(MONTH, theMonth, '19000101'), 120) AS colName,
	theMonth,
	totals
FROM	cteSource;

WITH cteMonths(theMonth, stopMonth)
AS (
	SELECT	MIN(theMonth) AS theMonth,
		MAX(theMonth) AS stopMonth
	FROM	#Data

	UNION ALL

	SELECT		theMonth + 1 AS theMonth,
			stopMonth
	FROM		cteMonths
	WHERE		theMonth < stopMonth
)
INSERT	#Data
	(
		colName,
		theMonth,
		totals
	)
SELECT	CONVERT(CHAR(7), DATEADD(MONTH, theMonth, '19000101'), 120) AS colName,
	theMonth,
	0 AS totals
FROM	cteMonths;

SELECT		d.colName,
		d.totals,
		f.runningTotals
FROM		#Data AS d
CROSS APPLY	(
			SELECT	SUM(x.totals)
			FROM	#Data AS x
			WHERE	x.theMonth <= d.theMonth
		) AS f(runningTotals)
ORDER BY	d.theMonth;

DROP TABLE #Data;

DECLARE @tbl_data TABLE 
	(
	colName nvarchar(50), 
	totals int
	)		
;WITH cteMonths(theMonth, stopMonth) AS
	( -- this CTE gets the months list
	SELECT
		DATEDIFF(MONTH, '19000101', MIN(DateCreated)) AS theMonth,
		DATEDIFF(MONTH, '19000101', MAX(DateCreated)) AS stopMonth
	FROM
		@tbl_Results
	UNION ALL
	SELECT
		theMonth + 1 AS theMonth,
		stopMonth
	FROM
		cteMonths
	WHERE
		(theMonth < stopMonth)
	)
INSERT INTO @tbl_data 
	(
	colName,
	totals
	)
SELECT
	CONVERT(CHAR(7), DATEADD(MONTH, m.theMonth, '19000101'), 120) AS colName,
	ISNULL(t.Items, 0) AS totals
FROM
	cteMonths AS m LEFT JOIN 
	(
	SELECT
		DATEDIFF(MONTH, '19000101', DateCreated) AS theMonth,
		COUNT(*) AS Items
	FROM
		@tbl_Results
	GROUP BY
		DATEDIFF(MONTH, '19000101', DateCreated)) AS t ON t.theMonth = m.theMonth
ORDER BY
	m.theMonth

;WITH cteFinal(colName, totals) AS
	(
	SELECT 
		colName,
		totals
	FROM 
		@tbl_data 
	)
SELECT
	*
FROM
	cteFinal c1 CROSS APPLY
	(
	SELECT
		SUM(totals) as ct
	FROM
		cteFinal c2
	WHERE
		c2.colName <= c1.colName
	) b
ORDER BY
	c1.colName

DECLARE @tbl_data TABLE	
		(
			colName CHAR(7) NOT NULL,
			theMonth INT NOT NULL,
			totals INT NOT NULL
		);
WITH cteSource(theMonth, totals)
AS (
	SELECT		DATEDIFF(MONTH, '19000101', DateCreated) AS theMonth,
			COUNT(*) AS totals
	FROM		@Tbl_Results
	GROUP BY	DATEDIFF(MONTH, '19000101', DateCreated)
)
INSERT INTO	@tbl_data
	(
		colName,
		theMonth,
		totals
	)
SELECT	CONVERT(CHAR(7), DATEADD(MONTH, theMonth, '19000101'), 120) AS colName,
	theMonth,
	totals
FROM	cteSource;
WITH cteMonths(theMonth, stopMonth)
AS (
	SELECT	MIN(theMonth) AS theMonth,
		MAX(theMonth) AS stopMonth
	FROM	@tbl_data

	UNION ALL

	SELECT		theMonth + 1 AS theMonth,
			stopMonth
	FROM		cteMonths
	WHERE		theMonth < stopMonth
)
INSERT	@tbl_data
	(
		colName,
		theMonth,
		totals
	)
SELECT	CONVERT(CHAR(7), DATEADD(MONTH, theMonth, '19000101'), 120) AS colName,
	theMonth,
	0 AS totals
FROM	cteMonths;

SELECT		d.colName,
		d.totals,
		f.runningTotals
FROM		@tbl_data AS d
CROSS APPLY	(
			SELECT	SUM(x.totals)
			FROM	@tbl_data AS x
			WHERE	x.theMonth <= d.theMonth
		) AS f(runningTotals)
ORDER BY	d.theMonth

-- Prevent unwanted resultsets back to client
SET NOCOUNT ON;

-- Prepare staging table
CREATE TABLE	#Data
		(
			theMonth INT PRIMARY KEY CLUSTERED,
			totals INT NOT NULL
		);

-- Populate staging table with all months
WITH cteMonths(theMonth, stopMonth)
AS (
	-- This anchor query should have the same filters as your query which populate @Tbl_Results table
	SELECT	DATEDIFF(MONTH, '19000101', MIN(DateCreated)) AS theMonth,
		DATEDIFF(MONTH, '19000101', MAX(DateCreated)) AS stopMonth
	FROM	{Your schema name here}.{Your source table name here}

	UNION ALL

	SELECT		theMonth + 1 AS theMonth,
			stopMonth
	FROM		cteMonths
	WHERE		theMonth < stopMonth
)
INSERT		#Data
		(
			theMonth,
			totals
		)
SELECT		theMonth,
		0 AS totals
FROM		cteMonths
ORDER BY	theMonth;

-- Update staging table with total count for each month
UPDATE		d
SET		d.totals = w.totals
FROM		#Data AS d
INNER JOIN	(
			-- This aggregated query should have the same filters as your query which populate @Tbl_Results table
			SELECT		DATEDIFF(MONTH, '19000101', DateCreated) AS theMonth,
					COUNT(*) AS totals
			FROM		{Your schema name here}.{Your source table name here}
			GROUP BY	DATEDIFF(MONTH, '19000101', DateCreated)
		) AS w ON w.theMonth = d.theMonth;

-- Display the final result including a running total
SELECT		CONVERT(CHAR(7), DATEADD(MONTH, d.theMonth, '19000101'), 120) AS colName,
		d.totals,
		f.runningTotals
FROM		#Data AS d
CROSS APPLY	(
			SELECT	SUM(x.totals)
			FROM	#Data AS x
			WHERE	x.theMonth <= d.theMonth
		) AS f(runningTotals)
ORDER BY	d.theMonth;

-- Clean up
DROP TABLE #Data;

-- Prevent unwanted resultsets back to client
SET NOCOUNT ON;

/*
SELECT	COUNT(*)
FROM	Sales.factSalesDetail
-- 100 760 501 row(s) affected
*/

-- Prepare staging table
CREATE TABLE	#Data
		(
			theMonth INT PRIMARY KEY CLUSTERED,
			totals INT NOT NULL
		);

-- Populate staging table with all months
WITH cteMonths(theMonth, stopMonth)
AS (
	SELECT	DATEDIFF(MONTH, '19000101', MIN(ssdLastKnownDate)) AS theMonth,
		DATEDIFF(MONTH, '19000101', MAX(ssdLastKnownDate)) AS stopMonth
	FROM	Sales.factSalesDetail

	UNION ALL

	SELECT		theMonth + 1 AS theMonth,
			stopMonth
	FROM		cteMonths
	WHERE		theMonth < stopMonth
)
INSERT		#Data
		(
			theMonth,
			totals
		)
SELECT		theMonth,
		0 AS totals
FROM		cteMonths
ORDER BY	theMonth
OPTION		(MAXRECURSION 0);

-- Update staging table with total count for each month
UPDATE		d
SET		d.totals = w.totals
FROM		#Data AS d
INNER JOIN	(
			-- This aggregated query should have the same filters as your query which populate @Tbl_Results table
			SELECT		DATEDIFF(MONTH, '19000101', ssdLastKnownDate) AS theMonth,
					COUNT(*) AS totals
			FROM		Sales.factSalesDetail
			GROUP BY	DATEDIFF(MONTH, '19000101', ssdLastKnownDate)
		) AS w ON w.theMonth = d.theMonth;

-- Display the final result including a running total
SELECT		CONVERT(CHAR(7), DATEADD(MONTH, d.theMonth, '19000101'), 120) AS colName,
		d.totals,
		f.runningTotals
FROM		#Data AS d
CROSS APPLY	(
			SELECT	SUM(x.totals)
			FROM	#Data AS x
			WHERE	x.theMonth <= d.theMonth
		) AS f(runningTotals)
ORDER BY	d.theMonth;

-- Clean up
DROP TABLE #Data;

Client Execution Time	16:10:05		16:09:57		16:09:35		
Query Profile Statistics							
  Number of INSERT, DELETE and UPDATE statements	2		2		2		2.0000
  Rows affected by INSERT, DELETE, or UPDATE statements	0		0		0		0.0000
  Number of SELECT statements 	1		1		1		1.0000
  Rows returned by SELECT statements	0		0		0		0.0000
  Number of transactions 	2		2		2		2.0000
Network Statistics							
  Number of server roundtrips	1		1		1		1.0000
  TDS packets sent from client	1		1		1		1.0000
  TDS packets received from server	7		7		7		7.0000
  Bytes sent from client	3266		3266		3266		3266.0000
  Bytes received from server	25895		25895		25895		25895.0000
Time Statistics							
  Client processing time	109		109		110		109.3333
  Total execution time	6535		6457		6442		6478.0000
  Wait time on server replies	6426		6348		6332		6368.6670