Arrange Date - 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

General SQL Server Forums

New to SQL Server Programming

Arrange Date

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-03-24 : 07:58:00

Hi, I have the follwoing query which gives the results as shown;

SELECT     CASE Month(RegistrationDate) 
                      WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN 8 THEN 'Aug' WHEN
                       9 THEN 'Sep' WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END AS Month, YEAR(RegistrationDate) AS Year, COUNT(*) AS Total
FROM         dbo.aspnet_cprdContacts
GROUP BY MONTH(RegistrationDate), YEAR(RegistrationDate)

Results:

Month Year Total
Apr 2012 168
Jan 2014 56
May 2013 67
Sep 2012 11
Oct 2013 100
Feb 2014 45

Now, I want my result to be;

Month Year Total
Apr 2012 168
Sep 2012 11
May 2013 67
Oct 2013 100
Jan 2014 56
Feb 2014 45

Any help highly appreciated..

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-24 : 08:01:48

[code]ORDER BY YEAR(RegistrationDate), MONTH(RegistrationDate)[/code]

also you don't need the case when to get the month name.
[code]
SELECT
LEFT(DATENAME(MONTH, RegistrationDate), 3) AS [Month],
YEAR(RegistrationDate) AS [Year],
COUNT(*) AS Total
FROM
dbo.aspnet_cprdContacts
GROUP BY
YEAR(RegistrationDate),
MONTH(RegistrationDate)
ORDER BY
YEAR(RegistrationDate),
MONTH(RegistrationDate)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-03-24 : 08:22:56

In SQL I receive the error message;

Column 'dbo.aspnet_cprdContacts.RegistrationDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-24 : 08:43:29

sorry . .

SELECT	
	LEFT(DATENAME(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, RegistrationDate), 0)), 3) AS [Month],
	YEAR(DATEADD(MONTH, DATEDIFF(MONTH, 0, RegistrationDate), 0)) AS [Year], 
	COUNT(*) AS Total
FROM	
	dbo.aspnet_cprdContacts
GROUP BY
	DATEADD(MONTH, DATEDIFF(MONTH, 0, RegistrationDate), 0)
ORDER BY
	[Year], [Month]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources