return zero for months where no data exsits

  • Hi,

    I have a table (Users) with rows containing data about registered user,

    Table Columns: UserId, RegisteredDate

    I want to know how many users have registered every month in the last six months.

    I have created following query

    select

    COUNT(*) AS NewUsers,

    MONTH(RegisteredDate) AS MNTH,

    YEAR(RegisteredDate) AS YR

    from Users

    where RegisteredDate BETWEEN dateadd(m, -6, GETDATE()) AND dateadd(m, 0, GETDATE())

    GROUP BY MONTH(RegisteredDate), YEAR(RegisteredDate)

    This returns as expected:

    Cnt, MNTH, YR

    13, 8, 2012

    4, 9, 2012

    5, 10, 2012

    6, 11, 2012

    7, 12, 2012

    8, 1, 2013

    =======================================================================

    Question: on certain months if there are no registered users, how do i return zero value

    E.g

    Cnt, MNTH, YR

    13, 8, 2012

    4, 9, 2012

    5, 10, 2012

    0, 11, 2012 -- no users registered in this month

    7, 12, 2012

    8, 1, 2013

    ========================================================================

    Appreciate any input.

    Thanks

  • 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


    --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!

  • I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'

    That looks like cool local dialect for MySql. How can we port that to ANSI sql?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

  • Sean Lange (1/4/2013)


    I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'

    That looks like cool local dialect for MySql. How can we port that to ANSI sql?

    And something else I don't get: as much as Joe posts responses here, you would think he actually cared about helping people. But I know we've all seen this *exact* same response of his umpteen times. If he cared he would not be spamming us. But he *is* spamming us. So he *must* not care. So why is he here again?

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • 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!

  • 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

  • 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

    You have effectively turned your left join into an inner join because you filter out the rows in your where clause. Move the where condition to your join and you should get what you are looking for.

    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())

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks.

    Here is the final SQL i have:

    I created a ReportDates table with the month, year

    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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply