Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

return zero for months where no data exsits Expand / Collapse
Author
Message
Posted Friday, January 4, 2013 12:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 11:56 PM
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



Post #1403079
Posted Friday, January 4, 2013 1:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:54 PM
Points: 12,910, Visits: 32,016
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1403086
Posted Friday, January 4, 2013 1:59 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 1,945, Visits: 2,928
I have a table (Users) with rows containing data about registered user,
Table Columns: UserId, RegisteredDate


Please learn to post DDL and not narratives. This is basic Netiquette.

Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Something_Report_Periods
(something_report_name CHAR(10) NOT NULL PRIMARY KEY
CHECK (something_report_name LIKE <pattern>),
something_report_start_date DATE NOT NULL,
something_report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (something_report_start_date <= something_report_end_date),
etc);

These report periods can overlap or have gaps. 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'

SELECT COUNT(U.user_id) AS new_user_cnt, R.period_name
FROM Report_Periods AS R
LEFT OUTER JOIN
Users AS U
ON U.registration_date AS R
BETWEEN R.month_report_start_date AND R.month_report_end_date
GROUP BY R.period_name;


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1403106
Posted Friday, January 4, 2013 2:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168

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)
Post #1403115
Posted Friday, January 4, 2013 2:18 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,566, Visits: 2,392
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.
Post #1403117
Posted Friday, January 4, 2013 2:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:06 PM
Points: 1,566, Visits: 2,392
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.
Post #1403120
Posted Friday, January 4, 2013 2:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:54 PM
Points: 12,910, Visits: 32,016
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1403126
Posted Friday, January 4, 2013 4:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 11:56 PM
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

Post #1403158
Posted Friday, January 4, 2013 7:47 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 1,945, Visits: 2,928
That looks like cool local dialect for MySql. How can we port that to ANSI SQL ?


There are some proposal to add it to ISO-8601; I do not know the status. For now, I use them as names for report periods because they sort so easily to the top of the ISO-8601 calendar dates when cast SQL temporal data to local host language strings.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1403186
Posted Monday, January 7, 2013 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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)
Post #1403599
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse