madhavsinghk (1/4/2013)

---

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