Order by 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

Order by Date

Author

Topic

golyath
Starting Member

21 Posts

Posted - 2007-10-10 : 05:19:47

Hi,

I have this statement:

SELECT DATENAME(month, date) AS Month, COUNT(*) AS Total
FROM Table
GROUP BY DATENAME(month, date)

I want to order it in month order...

If i order it by DATENAME(month, date) then it is just in alphabetical.

Also is there anyway that all months are added to the result even if there are no records for that month (So it will just show 0 in the count)

Thanks for anyhelp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 05:28:40

[code]SELECT DATENAME(month, date) AS Month,
COUNT(*) AS Total
FROM Table
GROUP BY DATENAME(month, date),
month(date)
order by month(date)[/code]

E 12°55'05.25"
N 56°04'39.16"

golyath
Starting Member

21 Posts

Posted - 2007-10-10 : 05:34:43

Thanks,

This has sorted out the order problem.

Does anyone have any suggestions for the problem of the missing months?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-10 : 05:37:29

How about this?

declare @t table
(
	[Date] datetime,
	value int
)

insert @t
select '20070101', 3 union all
select '20070111', 5 union all
select '20070203', 11 union all
select '20070315', 73 union all
select '20070501', 76 union all
select '20070614', 11 union all
select '20070802', 17 union all
select '20070809', 33 union all
select '20070907', 47 union all
select '20071116', 45 union all
select '20071201', 89 

select 
	datename(month, dateadd(month, t1.number, '20061201')), 
	sum(case when t2.value is not null then 1 else 0 end)
from 
	master..spt_values t1 Left Join @t t2 on datename(month, dateadd(month, t1.number, '20061201')) = Datename(month, t2.[Date])
where 
	name is null and number between 1 and 12
Group by
	datename(month, dateadd(month, t1.number, '20061201')), t1.number
order by
	t1.number

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 05:41:51

[code]SELECT DATENAME(month, q.date) AS Month,
SUM(q.t) AS Total
FROM (
SELECT Date, 1 AS t FROM Table UNION ALL
SELECT '19000101', 0 UNION ALL
SELECT '19000201', 0 UNION ALL
SELECT '19000301', 0 UNION ALL
SELECT '19000401', 0 UNION ALL
SELECT '19000501', 0 UNION ALL
SELECT '19000601', 0 UNION ALL
SELECT '19000701', 0 UNION ALL
SELECT '19000801', 0 UNION ALL
SELECT '19000901', 0 UNION ALL
SELECT '19001001', 0 UNION ALL
SELECT '19001101', 0 UNION ALL
SELECT '19001201', 0
) AS q
GROUP BY DATENAME(month, q.date),
month(q.date)
order by month(q.date)[/code]

E 12°55'05.25"
N 56°04'39.16"

golyath
Starting Member

21 Posts

Posted - 2007-10-10 : 05:47:04

Thanks,

I shall try both :-)

golyath
Starting Member

21 Posts

Posted - 2007-10-10 : 08:38:21

It works perfectly thanks.

Can anyone explain why when i added:

WHERE date != '' AND DATENAME(year, date) = '2008'

It then restricts it back to only the months with data.
I added it just abouve the GROUP BY.

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-10 : 09:39:48

If you want to group your data by month, consider reading this:

http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx

It is much better and easier to group on actual datetime values, not month names -- they won't sort correctly. Then, on your report or at your client, you can easily format those months any way that you want without forcing it to be done in T-SQL.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources