datename 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

SQL Server 2000 Forums

Transact-SQL (2000)

datename query

Author

Topic

cdgregory
Starting Member

9 Posts

Posted - 2004-11-09 : 04:18:39

Hello,

this query returns the amount of reports created by month, how can I make it order the months in month order??:

SELECT COUNT(dbo.Reports.ReportID) AS [report id count], DATENAME([month], dbo.Reports.ReportCreated) AS [month name]
FROM dbo.Reports INNER JOIN
dbo.SYS_ReportTypes ON dbo.Reports.ReportTypeID = dbo.SYS_ReportTypes.ReportTypeID
WHERE (dbo.Reports.ReportCreated > CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
GROUP BY DATENAME([MONTH], dbo.Reports.ReportCreated), dbo.SYS_ReportTypes.ReportTypeID
HAVING (dbo.SYS_ReportTypes.ReportTypeID = 1)

Many thanks!!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-09 : 04:23:15

quote:
Originally posted by cdgregory

Hello,

this query returns the amount of reports created by month, how can I make it order the months in month order??:

SELECT COUNT(dbo.Reports.ReportID) AS [report id count], DATENAME([month], dbo.Reports.ReportCreated) AS [month name]
FROM dbo.Reports INNER JOIN
dbo.SYS_ReportTypes ON dbo.Reports.ReportTypeID = dbo.SYS_ReportTypes.ReportTypeID
WHERE (dbo.Reports.ReportCreated > CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
GROUP BY DATENAME([MONTH], dbo.Reports.ReportCreated), dbo.SYS_ReportTypes.ReportTypeID
HAVING (dbo.SYS_ReportTypes.ReportTypeID = 1)

Many thanks!!

add an order by clause at the end of the query?

--------------------
keeping it simple...

cdgregory
Starting Member

9 Posts

Posted - 2004-11-09 : 04:26:36

maybe I am missing something, but if I order it, it orders alphabetically whereas I want it in January, February etc etc

samrat
Yak Posting Veteran

94 Posts

Posted - 2004-11-09 : 04:45:19

Try...

Order By DATEPART([month], dbo.Reports.ReportCreated)

HTH

Samrat

cdgregory
Starting Member

9 Posts

Posted - 2004-11-09 : 05:59:25

lastest change:
SELECT TOP 100 PERCENT COUNT(dbo.Reports.ReportID) AS [bugs raised], DATENAME([month], dbo.Reports.ReportCreated) AS [month]
FROM dbo.Reports INNER JOIN
dbo.SYS_ReportTypes ON dbo.Reports.ReportTypeID = dbo.SYS_ReportTypes.ReportTypeID INNER JOIN
dbo.ReportAssignees ON dbo.Reports.ReportID = dbo.ReportAssignees.ReportID INNER JOIN
dbo.Users ON dbo.ReportAssignees.AssigneeID = dbo.Users.UserID
WHERE (dbo.Reports.ReportCreated > CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
GROUP BY DATENAME([MONTH], dbo.Reports.ReportCreated), dbo.SYS_ReportTypes.ReportTypeID
HAVING (dbo.SYS_ReportTypes.ReportTypeID = 1)
ORDER BY DATENAME([month], dbo.Reports.ReportCreated)

if I change the last line to DATEPART I get a column name dbo.reports.reportcreated is invalid in order by clause because it is not contained in either an aggrega etc

where am I going wrong?

samrat
Yak Posting Veteran

94 Posts

Posted - 2004-11-09 : 22:07:47

Change the Group BY Clause to following

GROUP BY DATENAME([MONTH], dbo.Reports.ReportCreated), dbo.SYS_ReportTypes.ReportTypeID,DATENAME([month], dbo.Reports.ReportCreated)

Samrat

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-10 : 00:58:42

quote:
Originally posted by cdgregory

lastest change:
SELECT TOP 100 PERCENT COUNT(dbo.Reports.ReportID) AS [bugs raised], DATENAME([month], dbo.Reports.ReportCreated) AS [month]
FROM dbo.Reports INNER JOIN
dbo.SYS_ReportTypes ON dbo.Reports.ReportTypeID = dbo.SYS_ReportTypes.ReportTypeID INNER JOIN
dbo.ReportAssignees ON dbo.Reports.ReportID = dbo.ReportAssignees.ReportID INNER JOIN
dbo.Users ON dbo.ReportAssignees.AssigneeID = dbo.Users.UserID
WHERE (dbo.Reports.ReportCreated > CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
GROUP BY DATENAME([MONTH], dbo.Reports.ReportCreated), dbo.SYS_ReportTypes.ReportTypeID
HAVING (dbo.SYS_ReportTypes.ReportTypeID = 1)
ORDER BY DATENAME([month], dbo.Reports.ReportCreated)

this doesn't work?

--------------------
keeping it simple...

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-11-10 : 01:57:15

Jen
That does work but as cdgregory said they want the results ordered in "date" order
eg
Jan, Feb, Mar etc
not
Apr, Aug, Dec

Why not just add the DATEPART function to the SELECT list
Something like this (on Northwind)

SELECT DATEPART(m,Orderdate) AS MonthNo, DATENAME(m,OrderDate) AS MonthTxt, COUNT(OrderId) AS NoOfOrders
FROM Orders
GROUP BY DATEPART(m,Orderdate), DATENAME(m,Orderdate)
ORDER BY DATEPART(m,Orderdate)

Andy

cdgregory
Starting Member

9 Posts

Posted - 2004-11-10 : 07:39:04

AndyB13, spot on - that works for me.

Many Thanks

Kristen
Test

22859 Posts

Posted - 2004-11-10 : 08:08:03

Does the Orderdate not span a year?

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 10:33:11

Yes, as Kristen states, whenever you GROUP BY a month that is simply a value from 1-12, you should always add a YEAR() grouping as well in your query. Even if you are "sure" that the data will always only cover one year.

i.e., using Andy's code:

SELECT Year(OrderDate) as Year, DATEPART(m,Orderdate) AS MonthNo, DATENAME(m,OrderDate) AS MonthTxt, COUNT(OrderId) AS NoOfOrders
FROM Orders
GROUP BY Year(OrderDate), DATEPART(m,Orderdate), DATENAME(m,Orderdate)
ORDER BY Year(OrderDate), DATEPART(m,Orderdate)

- Jeff

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-11-10 : 10:50:30

Thanks Kristen & Jeff, I should of pointed that out or at least put a WHERE statement to only show a selected year

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources