Help Requested for DATEPART & CASE

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

SQL Server Development (2000)

Help Requested for DATEPART & CASE

Author

Topic

Woodzy
Starting Member

7 Posts

Posted - 2005-01-14 : 20:30:18

Hello,
I'm fairly new to SQL and could use a little advise. I've been stuck on this problem for a while as I've become frustrated. I just would like to know if I'm on the right track for accomplishing the below requirements. Any help or pointer would be appreciated!!

I'm using the Northwinds DB in SQL Server 2000 to
list the months when orders are taken and the total number of orders for that month. (HINT: Use DATEPART and CASE). Use Month abbreviations instead of month numbers (ex. Jan instead of 1, Feb instead of 2, etc…). Order by the month.

Here is what I have so far, it works with the commented "CASE". When I remove the comment from the "CASE" I get this error "Incorrect syntax near the keyword 'THEN'. I
SELECT OrderDate, Count (OrderID) as 'Total Orders per Month', DATEPART(mm, OrderDate) as 'Month',
CASE
WHEN '12' THEN 'Dec'
WHEN '11' THEN 'Nov'
WHEN '10' THEN 'Oct'
WHEN '9' THEN 'Sept'
WHEN '8' THEN 'Aug'
WHEN '7' THEN 'Jul'
WHEN '6' THEN 'Jun'
WHEN '5' THEN 'May'
WHEN '4' THEN 'Apr'
WHEN '3' THEN 'Mar'
WHEN '2' THEN 'Feb'
WHEN '1' THEN 'Jan'
END AS 'MONTH'
FROM Orders
GROUP BY DATEPART(mm, OrderDate), OrderDate
ORDER BY 'Month Number' DESC

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-14 : 20:40:53

You need a comma before case.

You could also use left(datename(mm, OrderDate),3)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Woodzy
Starting Member

7 Posts

Posted - 2005-01-14 : 23:02:03

Thanks for the Tip! Now the error message changed to 'THEN.' Am I on the right track?

quote:
Originally posted by nr

You need a comma before case.

You could also use left(datename(mm, OrderDate),3)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-15 : 02:11:45

[code]

SELECT
OrderDate,
Count (OrderID) as 'Total Orders per Month',
DATEPART(mm, OrderDate) as 'Month',
CASE DATEPART(mm, OrderDate)
WHEN '12' THEN 'Dec'
WHEN '11' THEN 'Nov'
WHEN '10' THEN 'Oct'
WHEN '9' THEN 'Sept'
WHEN '8' THEN 'Aug'
WHEN '7' THEN 'Jul'
WHEN '6' THEN 'Jun'
WHEN '5' THEN 'May'
WHEN '4' THEN 'Apr'
WHEN '3' THEN 'Mar'
WHEN '2' THEN 'Feb'
WHEN '1' THEN 'Jan'
END AS 'MONTH'
FROM
Orders
GROUP BY
DATEPART(mm, OrderDate),
OrderDate
ORDER BY
DATEPART(mm, OrderDate) DESC
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-15 : 06:39:08

You want the number of orders per month so should not have orderdate in the group by. That will give one group for each date rather than for the month.
datepart gives an integer result not varchar so it should be WHEN 12 THEN 'Dec'.
You can use datename to get the month name.
count(*) would be better than Count (OrderID) for the number of records.
Use [] to delimit identifiers

so the query becomes
SELECT
Count (*) as [Total Orders per Month] ,
DATEPART(mm, OrderDate) as [Month Number] ,
max(left(DATENAME(mm,OrderDate),3)) AS [Month Name]
FROM
Orders
GROUP BY
DATEPART(mm, OrderDate)
ORDER BY
[Month Number] DESC

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

imughal
Posting Yak Master

192 Posts

Posted - 2005-01-15 : 08:15:23

Woodzy
Starting Member

7 Posts

Posted - 2005-01-15 : 16:14:14

To All,
Thanks for everyone's input and help regarding this query!!! The light bulbs are starting to become a little brighter.
Woodzy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources