return zero for months where no data exsits

select
COUNT(*) AS NewUsers,
AllPossibleMonthsAndYears.Month AS MNTH,
AllPossibleMonthsAndYears.Year AS YR

from AllPossibleMonthsAndYears
INNER JOIN Users ON MONTH(RegisteredDate) = AllPossibleMonthsAndYears.Month
AND YEAR(RegisteredDate) = AllPossibleMonthsAndYears.Year
where RegisteredDate BETWEEN dateadd(m, -6, GETDATE()) AND dateadd(m, 0, GETDATE())

GROUP BY
AllPossibleMonthsAndYears.Month,
AllPossibleMonthsAndYears.Year

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

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
28 6 2012
49 7 2012
8 8 2012
39 9 2012
4 10 2012
9 11 2012
10 12 2012
5 1 2013

	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

from #AllDates
LEFT OUTER JOIN Users ON MONTH(RegisteredDate) = #AllDates.ThisDateMonth
AND YEAR(RegisteredDate) = #AllDates.ThisDateYear
AND RegisteredDate BETWEEN dateadd(m, @span, GETDATE()) AND dateadd(m, 0, GETDATE())