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 Tuesday, January 8, 2013 4:10 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
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

Post #1404487
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse