Show all months - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Show all months

Author

Topic

drsquirrel
Starting Member

2 Posts

Posted - 2009-03-30 : 06:34:47

A query I am using, grouped by month, misses certain months. I know how I can show all of these, but when I try and filter my dates to a specific year it no longer works.



--example table
CREATE TABLE #PURecords ( PU_DateTime datetime NOT NULL, PU_TRP decimal (6, 2) NOT NULL )
--example 2009
INSERT INTO #PURecords VALUES ('01 January 2009', 10) INSERT INTO #PURecords VALUES ('02 January 2009', 11) INSERT INTO #PURecords VALUES ('03 January 2009', 12)
INSERT INTO #PURecords VALUES ('01 February 2009', 20) INSERT INTO #PURecords VALUES ('02 February 2009', 21) INSERT INTO #PURecords VALUES ('03 February 2009', 22)
INSERT INTO #PURecords VALUES ('01 March 2009', 30) INSERT INTO #PURecords VALUES ('02 March 2009', 31) INSERT INTO #PURecords VALUES ('03 March 2009', 32)
INSERT INTO #PURecords VALUES ('01 April 2009', 40)INSERT INTO #PURecords VALUES ('02 April 2009', 41) INSERT INTO #PURecords VALUES ('03 April 2009', 42)
--example 2008
INSERT INTO #PURecords VALUES ('01 January 2008', 110) INSERT INTO #PURecords VALUES ('02 January 2008', 111) INSERT INTO #PURecords VALUES ('03 January 2008', 112)
INSERT INTO #PURecords VALUES ('01 February 2008', 120) INSERT INTO #PURecords VALUES ('02 February 2008', 121) INSERT INTO #PURecords VALUES ('03 February 2008', 122)
INSERT INTO #PURecords VALUES ('01 March 2008', 130) INSERT INTO #PURecords VALUES ('02 March 2008', 131) INSERT INTO #PURecords VALUES ('03 March 2008', 132)
INSERT INTO #PURecords VALUES ('01 April 2008', 140)INSERT INTO #PURecords VALUES ('02 April 2008', 141) INSERT INTO #PURecords VALUES ('03 April 2008', 142)


--SELECT * FROM #PURecords

SELECT MonthList.Months, COALESCE(AVG(PU_TRP),0) As PU_TRP_AVG
FROM #PURecords
RIGHT JOIN (SELECT 1 As Months UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) As MonthList
ON DATEPART(month, PU_DateTime) = MonthList.Months
WHERE DATEPART(year, PU_DateTime) = '2009'
GROUP BY MonthList.Months

DROP TABLE #PURecords

Try --WHERE DATEPART(year, PU_DateTime) = '2009'
and it shows them all (but shows the average for each year)

Thanks :)

matty
Posting Yak Master

161 Posts

Posted - 2009-03-30 : 06:52:00

quote:

Originally posted by drsquirrel

A query I am using, grouped by month, misses certain months. I know how I can show all of these, but when I try and filter my dates to a specific year it no longer works.



--example table
CREATE TABLE #PURecords ( PU_DateTime datetime NOT NULL, PU_TRP decimal (6, 2) NOT NULL )
--example 2009
INSERT INTO #PURecords VALUES ('01 January 2009', 10) INSERT INTO #PURecords VALUES ('02 January 2009', 11) INSERT INTO #PURecords VALUES ('03 January 2009', 12)
INSERT INTO #PURecords VALUES ('01 February 2009', 20) INSERT INTO #PURecords VALUES ('02 February 2009', 21) INSERT INTO #PURecords VALUES ('03 February 2009', 22)
INSERT INTO #PURecords VALUES ('01 March 2009', 30) INSERT INTO #PURecords VALUES ('02 March 2009', 31) INSERT INTO #PURecords VALUES ('03 March 2009', 32)
INSERT INTO #PURecords VALUES ('01 April 2009', 40)INSERT INTO #PURecords VALUES ('02 April 2009', 41) INSERT INTO #PURecords VALUES ('03 April 2009', 42)
--example 2008
INSERT INTO #PURecords VALUES ('01 January 2008', 110) INSERT INTO #PURecords VALUES ('02 January 2008', 111) INSERT INTO #PURecords VALUES ('03 January 2008', 112)
INSERT INTO #PURecords VALUES ('01 February 2008', 120) INSERT INTO #PURecords VALUES ('02 February 2008', 121) INSERT INTO #PURecords VALUES ('03 February 2008', 122)
INSERT INTO #PURecords VALUES ('01 March 2008', 130) INSERT INTO #PURecords VALUES ('02 March 2008', 131) INSERT INTO #PURecords VALUES ('03 March 2008', 132)
INSERT INTO #PURecords VALUES ('01 April 2008', 140)INSERT INTO #PURecords VALUES ('02 April 2008', 141) INSERT INTO #PURecords VALUES ('03 April 2008', 142)


--SELECT * FROM #PURecords

SELECT MonthList.Months, COALESCE(AVG(PU_TRP),0) As PU_TRP_AVG
FROM #PURecords
RIGHT JOIN (SELECT 1 As Months UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) As MonthList
ON DATEPART(month, PU_DateTime) = MonthList.Months
AND DATEPART(year, PU_DateTime) = '2009'
GROUP BY MonthList.Months

DROP TABLE #PURecords

Try --WHERE DATEPART(year, PU_DateTime) = '2009'
and it shows them all (but shows the average for each year)

Thanks :)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-30 : 06:52:48

Thats because of the where filter you have. Since you don't have records for months 5 and above, you see only first 4 month's data.
In other words, your right join is reduced to an inner join because of the where filter.
You need to put your filter in an ON clause instead.
Try this,

SELECT MonthList.Months, COALESCE(AVG(PU_TRP),0) As PU_TRP_AVG
FROM #PURecords
RIGHT JOIN (SELECT 1 As Months UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) As MonthList on DATEPART(year, PU_DateTime) = '2009'
and DATEPART(month, PU_DateTime) = MonthList.Months
GROUP BY MonthList.Months

drsquirrel
Starting Member

2 Posts

Posted - 2009-03-30 : 06:54:19

Idiot Mistake #4

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources