InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:43:04 GMT20http://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspxThanks. Here is the final SQL i have: I created a ReportDates table with the month, year [code="other"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 [/code]Tue, 08 Jan 2013 16:10:44 GMTmadhavsinghkhttp://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspx[quote][b]madhavsinghk (1/4/2013)[/b][hr]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 recordsNewUsers, Month, Year28 6 201249 7 20128 8 201239 9 20124 10 20129 11 201210 12 20125 1 2013[code="sql"] 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[/code][/quote]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.[code]from #AllDatesLEFT 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())[/code]Mon, 07 Jan 2013 07:30:53 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspx[quote]That looks like cool local dialect for MySql. How can we port that to ANSI SQL ?[/quote]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.Fri, 04 Jan 2013 19:47:15 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspxHi,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 recordsNewUsers, Month, Year28 6 201249 7 20128 8 201239 9 20124 10 20129 11 201210 12 20125 1 2013[code="sql"] 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[/code]Fri, 04 Jan 2013 16:35:56 GMTmadhavsinghkhttp://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspx[quote][b]Greg Snidow (1/4/2013)[/b][hr][quote][b]Lowell (1/4/2013)[/b][hr]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:[code]select COUNT(*) AS NewUsers,AllPossibleMonthsAndYears.Month AS MNTH,AllPossibleMonthsAndYears.Year AS YRfrom AllPossibleMonthsAndYearsINNER JOIN Users ON MONTH(RegisteredDate) = AllPossibleMonthsAndYears.MonthAND YEAR(RegisteredDate) = AllPossibleMonthsAndYears.Yearwhere RegisteredDate BETWEEN dateadd(m, -6, GETDATE()) AND dateadd(m, 0, GETDATE())GROUP BY AllPossibleMonthsAndYears.Month,AllPossibleMonthsAndYears.Year[/code][/quote]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?[/quote]Doh! yes;Greg is absolutely correct;too quick on the response, since i didn't have real DDL to play withFri, 04 Jan 2013 14:33:21 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspx[quote][b]Sean Lange (1/4/2013)[/b][hr][quote]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'[/quote]That looks like cool local dialect for MySql. How can we port that to ANSI sql?[/quote]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?Fri, 04 Jan 2013 14:24:06 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspx[quote][b]Lowell (1/4/2013)[/b][hr]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:[code]select COUNT(*) AS NewUsers,AllPossibleMonthsAndYears.Month AS MNTH,AllPossibleMonthsAndYears.Year AS YRfrom AllPossibleMonthsAndYearsINNER JOIN Users ON MONTH(RegisteredDate) = AllPossibleMonthsAndYears.MonthAND YEAR(RegisteredDate) = AllPossibleMonthsAndYears.Yearwhere RegisteredDate BETWEEN dateadd(m, -6, GETDATE()) AND dateadd(m, 0, GETDATE())GROUP BY AllPossibleMonthsAndYears.Month,AllPossibleMonthsAndYears.Year[/code][/quote]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?Fri, 04 Jan 2013 14:18:57 GMTGreg Snidowhttp://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspx[quote]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'[/quote]That looks like cool local dialect for MySql. How can we port that to ANSI sql?Fri, 04 Jan 2013 14:08:48 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspx[quote] I have a table (Users) with rows containing data about registered user,Table Columns: UserId, RegisteredDate [/quote]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;Fri, 04 Jan 2013 13:59:06 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspxyou 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:[code]select COUNT(*) AS NewUsers,AllPossibleMonthsAndYears.Month AS MNTH,AllPossibleMonthsAndYears.Year AS YRfrom AllPossibleMonthsAndYearsINNER JOIN Users ON MONTH(RegisteredDate) = AllPossibleMonthsAndYears.MonthAND YEAR(RegisteredDate) = AllPossibleMonthsAndYears.Yearwhere RegisteredDate BETWEEN dateadd(m, -6, GETDATE()) AND dateadd(m, 0, GETDATE())GROUP BY AllPossibleMonthsAndYears.Month,AllPossibleMonthsAndYears.Year[/code]Fri, 04 Jan 2013 13:09:05 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1403079-392-1.aspxHi,I have a table (Users) with rows containing data about registered user,Table Columns: UserId, RegisteredDateI 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, YR13, 8, 20124, 9, 20125, 10, 20126, 11, 20127, 12, 20128, 1, 2013=======================================================================Question: on certain months if there are no registered users, how do i return zero value E.gCnt, MNTH, YR13, 8, 20124, 9, 20125, 10, 20120, 11, 2012 -- no users registered in this month7, 12, 20128, 1, 2013========================================================================Appreciate any input.ThanksFri, 04 Jan 2013 12:57:02 GMTmadhavsinghk