SELECT
	COUNT(*) AS totals,
	CAST(year([loginDate]) as nvarchar) + '-' + CAST(month([loginDate]) as nvarchar) as colName
FROM 
	@tmpTable 
GROUP BY
	datename(month, [loginDate])+' '+CAST(Year(loginDate) AS nvarchar), month([loginDate]), year([loginDate])
ORDER BY
	year(loginDate),
	month(loginDate)

totals  colName
-----------------
64	2006-12
139	2007-1
52	2007-3
121	2007-4

totals  colName
-----------------
64	2006-12
139	2007-1
0       2007-2  <<-- 'filled in'
52	2007-3
121	2007-4

declare	@start_date	datetime,
	@end_date	datetime

select	@start_date	= '2006-01-01',
	@end_date	= '2007-12-31'

; with 
dates as
(
	select	date_start 	= dateadd(month, n.number, @start_date),
		date_end	= dateadd(month, n.number + 1, @start_date)
	from	numbers n
	where	n.number	between 0 and datediff(month, @start_date, @end_date)
)
select	isnull(count(t.loginDate), 0) as totals,
	convert(varchar(7), d.date_start, 121)
from	dates d
	left join @tmpTable t	on	d.date_start	<= t.loginDate
				and	d.date_end	>  t.loginDate
group by convert(varchar(7), d.date_start, 121)

DECLARE @monthsYears TABLE (monthYear nvarchar(10))
DECLARE @mMin INT
DECLARE @mMax INT
DECLARE @yMin INT
DECLARE @yMax INT
DECLARE @minDate datetime
DECLARE @maxDate datetime
SET @minDate = (SELECT MIN([loginDate]) FROM @tmpTable)
SET @maxDate = (SELECT MAX([loginDate]) FROM @tmpTable)
SET @mMin = (SELECT MIN(MONTH(@minDate)))
SET @yMin = (SELECT MIN(YEAR(@minDate)))
SET @mMax = (SELECT MAX(MONTH(@maxDate)))
SET @yMax = (SELECT MAX(YEAR(@maxDate)))
PRINT @mMin -- '6'
PRINT @yMin -- '2006'
PRINT @mMax -- '5'
PRINT @yMax	-- '2011'			
WHILE (@mMin <= @mMax AND @yMin <= @yMax)
	BEGIN	
		-- ...............This never starts...............!
		
		INSERT INTO @monthsYears
			(
			monthYear
			)
		VALUES
			(
			cast(@mMin AS nvarchar) + '-' + cast(@yMin AS nvarchar)
			)
		SET @mMin = @mMin + 1
		IF @mMin > 12
			BEGIN
				SET @yMin = @yMin + 1
				SET @mMin = 1
			END
	END

SELECT * FROM @monthsYears -- No rows!

select	@minDate = MIN([loginDate]),
	@maxDate = MAX([loginDate])
FROM 	@tmpTable

while	@minDate	<= @maxDate
begin	
	INSERT INTO @monthsYears
	select	right(convert(varchar(10), @minDate, 105), 7)
	select	@minDate = dateadd(month, 1, @minDate)
end
SELECT * FROM @monthsYears

DECLARE @monthsYears TABLE (monthYear nvarchar(10))
DECLARE @mMin INT
DECLARE @mMax INT
DECLARE @yMin INT
DECLARE @yMax INT
DECLARE @minDate datetime
DECLARE @maxDate datetime
SET @minDate = (SELECT MIN([loginDate]) FROM @tmpTable)
SET @maxDate = (SELECT MAX([loginDate]) FROM @tmpTable)
SET @mMin = (SELECT MIN(MONTH(@minDate)))
SET @yMin = (SELECT MIN(YEAR(@minDate)))
SET @mMax = (SELECT MAX(MONTH(@maxDate)))
SET @yMax = (SELECT MAX(YEAR(@maxDate)))
PRINT @mMin -- '6'
PRINT @yMin -- '2006'
PRINT @mMax -- '5'
PRINT @yMax	-- '2011'	
DECLARE @dateReached bit
SET @dateReached = 0

WHILE @dateReached = 0
	BEGIN	
		IF @yMin >= @yMax AND @mMin >= @mMax
			BEGIN
				SET @dateReached = 1
			END							
		INSERT INTO @monthsYears
			(
			monthYear
			)
		VALUES
			(
			cast(@yMin AS nvarchar) + '-' + cast(@mMin AS nvarchar) 
			)
		SET @mMin = @mMin + 1
		IF @mMin > 12
			BEGIN
				SET @yMin = @yMin + 1
				SET @mMin = 1
			END
	END

SELECT * FROM @monthsYears

quote:

---

Originally posted by khtan

because you are getting the min & max of the month separately. So if you have logindate like 2010-12-02 and 2011-02-03 you will not get any result at all.

---

DECLARE @monthsYears TABLE (monthYear nvarchar(10))
DECLARE @minDate datetime
DECLARE @maxDate datetime
SET @minDate = (SELECT MIN([loginDate]) FROM @tmpTable)
SET @maxDate = (SELECT MAX([loginDate]) FROM @tmpTable)
WHILE @minDate <= @maxDate
	BEGIN					
		INSERT INTO @monthsYears
			(
			monthYear
			)
		VALUES
			(
			cast(YEAR(@minDate) AS nvarchar) + '-' + cast(MONTH(@minDate) AS nvarchar) 
			)
		SET @minDate = DATEADD(Month, 1, @minDate)
	END					
SELECT * FROM @monthsYears

quote:

---

I tried your suggestion out but there was a problem. If the DAY integer of the end date is less than the DAY integer of the start date, it misses off the last month.

---

select	@minDate = dateadd(day, datediff(day, 0, MIN([loginDate])), 0),
	@maxDate = dateadd(day, datediff(day, 0, MAX([loginDate])), 0)
FROM 	@tmpTable

quote:

---

Originally posted by khtan
this will convert the day to 1st of the month and over come that.

select	@minDate = dateadd(day, datediff(day, 0, MIN([loginDate])), 0),
	@maxDate = dateadd(day, datediff(day, 0, MAX([loginDate])), 0)
FROM 	@tmpTable

---

select	@minDate = dateadd(month, datediff(month, 0, MIN([loginDate])), 0),
	@maxDate = dateadd(month, datediff(month, 0, MAX([loginDate])), 0)
FROM 	@tmpTable

select  dateadd(day, datediff(day, 0, getdate()), 0),
        dateadd(month, datediff(month, 0, getdate()), 0),
        dateadd(year, datediff(year, 0, getdate()), 0)