SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [ReportDates](
	[DateMonth] [int] NULL,
	[DateMonthName] [nchar](10) NULL,
	[DateYear] [int] NULL,
	[ThisDate] [datetime] NULL
) ON [PRIMARY]

GO

==================================================================================================
--Populate ReportDates table

  	DECLARE @span int
	DECLARE @CurrentDate datetime
	DECLARE @StartRange datetime
	DECLARE @EndRange datetime
	DECLARE @tmp   datetime
	
	set @span = 0 --	 
	set @StartRange =  DATEADD(MM, @span, dateadd(yyyy, -5, GETDATE())) -- 2008-01-01
	set @EndRange =  DATEADD(MM, @span, dateadd(yyyy, 8, GETDATE())) --- 2020-12-01
		
    SET @CurrentDate = @StartRange

    -- insert all dates into temp table
    WHILE @CurrentDate <=  @EndRange
        BEGIN		
            
            SET @tmp =  DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrentDate), 0) 
            IF @CurrentDate < @EndRange           
				INSERT INTO ReportDates values(Month(@CurrentDate), DATENAME(MM, @CurrentDate), YEAR(@CurrentDate), @tmp)            
				
            SET @CurrentDate = dateadd(mm, 1, @CurrentDate)
            
        END
	
====================================================================================================

Select 				
		COUNT(OBJECTID) AS NewUsers,
		s.DateMonthName as MonthName,
		s.DateMonth AS MonthNumber,
		s.DateYear AS [Year]		
		
		FROM	 
    		(SELECT DISTINCT
        		DateMonth, DateYear, DateMonthName
    			FROM ReportDates
    			WHERE ThisDate BETWEEN DATEADD(MM, -6, GETDATE()) AND DATEADD(MM, 0, GETDATE())
    		) s  
    	LEFT JOIN Users ON MONTH(RegisteredDate) = s.DateMonth AND YEAR(RegisteredDate) = s.DateYear
		GROUP BY 	
			s.DateMonth,
			s.DateYear,
			s.DateMonthName
		ORDER BY s.DateYear