January 4, 2013 at 12:57 pm
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
January 4, 2013 at 1:09 pm
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
January 4, 2013 at 2:08 pm
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/
January 4, 2013 at 2:18 pm
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.
January 4, 2013 at 2:24 pm
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.
January 4, 2013 at 2:33 pm
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
January 4, 2013 at 4:35 pm
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
January 7, 2013 at 7:30 am
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/
January 8, 2013 at 4:10 pm
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