Hi,
Thanks for response, here is what i have so far:
If I set the monthly span to -10 (last 10 months), i only get records for last 8 months. No zero count returned for month 4,5 (in year 2012).
Query Results : 8 records
NewUsers, Month, Year
2862012
4972012
882012
3992012
4102012
9112012
10122012
512013
drop table #AllDates
DECLARE @span int
DECLARE @CurrentDate datetime
DECLARE @StartRange datetime
DECLARE @EndRange datetime
set @span = -10 --
set @StartRange = DATEADD(MM, @span, GETDATE())
set @EndRange = DATEADD(MM, 0, GETDATE()) --- current date
CREATE TABLE #AllDates (ThisDateMonth int , ThisDateYear int)
SET @CurrentDate = @StartRange
-- insert all dates into temp table
WHILE @CurrentDate <= @EndRange
BEGIN
INSERT INTO #AllDates values(Month(@CurrentDate),YEAR(@CurrentDate))
SET @CurrentDate = dateadd(mm, 1, @CurrentDate)
END
--Select * from #AllDates
Select
COUNT(*) AS NewUsers,
#AllDates.ThisDateMonth AS MNTH,
#AllDates.ThisDateYear AS YR
from #AllDates
LEFT OUTER JOIN Users ON MONTH(RegisteredDate) = #AllDates.ThisDateMonth
AND YEAR(RegisteredDate) = #AllDates.ThisDateYear
where RegisteredDate BETWEEN dateadd(m, @span, GETDATE()) AND dateadd(m, 0, GETDATE())
GROUP BY
#AllDates.ThisDateMonth,
#AllDates.ThisDateYear