Lowell (1/4/2013)
you need a Calendar table of some sort which gives you the arraay of all possible months;from that you change your query to select from that table, and join to your users table.
then you get sum() with zeros you are looking for:
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
Lowell, I mean no disrespect, but wouldn't he want to LEFT join on Users, then use the calendar table range in the WHERE clause?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.