Month and Year Query - 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

Other Forums

MS Access

Month and Year Query

Author

Topic

bobross80
Starting Member

11 Posts

Posted - 2010-01-07 : 23:55:16

I'm trying to write a query that will produce:

January 2010
January 2009
January 2008
January 2007
February 2010
February 2009
February 2008
February 2007
March 2010
March 2009
March 2008
March 2007
April 2010
April 2009
April 2008
April 2007
.
.
.
n

This is what I have so far:

SELECT MonthName(Month([Program Attendance].Date)) AS TableMonth, Year([Program Attendance].Date) AS TableYear
FROM [Program Attendance]
GROUP BY MonthName(Month([Program Attendance].Date)), Year([Program Attendance].Date)
ORDER BY Year([Program Attendance].Date) DESC, MonthName(Month([Program Attendance].Date));

Its grouping by month and year, and ordering by year and month, but orders by month alphabetically. When I remove the monthname() from the order by clause and just use month(), I get an error.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-08 : 01:22:19

What is the error message?

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-08 : 02:38:26

SELECT MonthName(Month([Program Attendance].Date)) AS TableMonth, Year([Program Attendance].Date) AS TableYear
FROM [Program Attendance]
GROUP BY MonthName(Month([Program Attendance].Date)), Year([Program Attendance].Date),Month([Program Attendance].Date)
ORDER BY Year([Program Attendance].Date) DESC, Month([Program Attendance].Date);

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-08 : 02:59:27

As OP stated this was what he has tried, hence we should no the error message...

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-08 : 03:49:40

quote:
Originally posted by webfred

As OP stated this was what he has tried, hence we should no the error message...

No, you're never too old to Yak'n'Roll if you're too young to die.

Note that I included an extra expression in the GROUP BY Clause

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-08 : 03:59:47

Aha!
I was blind. I think it is my age

quote:

SELECT MonthName(Month([Program Attendance].Date)) AS TableMonth, Year([Program Attendance].Date) AS TableYear
FROM [Program Attendance]
GROUP BY MonthName(Month([Program Attendance].Date)), Year([Program Attendance].Date),Month([Program Attendance].Date)
ORDER BY Year([Program Attendance].Date) DESC, Month([Program Attendance].Date);

No, you're never too old to Yak'n'Roll if you're too young to die.

bobross80
Starting Member

11 Posts

Posted - 2010-01-08 : 17:14:32

This is the output. So close! However, January 2007 should be grouped with January 2010 on the top...

Any ideas?

January 2010
February 2009
March 2008
January 2007
April 2007
May 2007

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-08 : 18:11:44

Then change the order by and it will work.

No, you're never too old to Yak'n'Roll if you're too young to die.

bobross80
Starting Member

11 Posts

Posted - 2010-01-09 : 13:35:52

Thank you! Thank you! Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources