Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


return zero for months where no data exsits


return zero for months where no data exsits

Author
Message
madhavsinghk
madhavsinghk
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 28
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949
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!

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
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.
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949
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!

madhavsinghk
madhavsinghk
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 28
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


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16550 Visits: 17004
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



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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search