• Greg Snidow (1/4/2013)


    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?

    Doh! yes;

    Greg is absolutely correct;

    too quick on the response, since i didn't have real DDL to play with

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!