May 23, 2013 at 10:27 am
All;
I am having an issue where my rows are calendar months and my columns are different vendors. I can get the information, but the months are sorted alphabetically not in calendar order. Here is my code:
I am having the same issue, but I'm using case statements to get the month names.:
SELECT Category, COUNT(Category) AS CatCount,
(CASE WHEN Month(OpenDate) = 1 THEN 'January'
WHEN Month(OpenDate) = 2 THEN 'February'
WHEN Month(OpenDate) = 3 THEN 'March'
WHEN Month(OpenDate) = 4 THEN 'April'
WHEN Month(OpenDate) = 5 THEN 'May'
WHEN Month(OpenDate) = 6 THEN 'June'
WHEN Month(OpenDate) = 7 THEN 'July'
WHEN Month(OpenDate) = 8 THEN 'August'
WHEN Month(OpenDate) = 9 THEN 'September'
WHEN Month(OpenDate) = 10 THEN 'October'
WHEN Month(OpenDate) = 11 THEN 'November'
WHEN Month(OpenDate) = 12 THEN 'December' END) AS mopendate
FROM Cloud_Tickets
WHERE (YEAR(OpenDate) = '2013')
GROUP BY MONTH(OpenDate), Category
ORDER BY MONTH(OpenDate)
Also, if there is a better way to do this, please enlighten me. I am a novice to programming.
Thank you
Brian
May 23, 2013 at 10:35 am
brian.geregach (5/23/2013)
All;I am having an issue where my rows are calendar months and my columns are different vendors. I can get the information, but the months are sorted alphabetically not in calendar order. Here is my code:
I am having the same issue, but I'm using case statements to get the month names.:
SELECT Category, COUNT(Category) AS CatCount,
(CASE WHEN Month(OpenDate) = 1 THEN 'January'
WHEN Month(OpenDate) = 2 THEN 'February'
WHEN Month(OpenDate) = 3 THEN 'March'
WHEN Month(OpenDate) = 4 THEN 'April'
WHEN Month(OpenDate) = 5 THEN 'May'
WHEN Month(OpenDate) = 6 THEN 'June'
WHEN Month(OpenDate) = 7 THEN 'July'
WHEN Month(OpenDate) = 8 THEN 'August'
WHEN Month(OpenDate) = 9 THEN 'September'
WHEN Month(OpenDate) = 10 THEN 'October'
WHEN Month(OpenDate) = 11 THEN 'November'
WHEN Month(OpenDate) = 12 THEN 'December' END) AS mopendate
FROM Cloud_Tickets
WHERE (YEAR(OpenDate) = '2013')
GROUP BY MONTH(OpenDate), Category
ORDER BY MONTH(OpenDate)
Also, if there is a better way to do this, please enlighten me. I am a novice to programming.
Thank you
Brian
No need to use a case to get the month name. You can use the DATENAME function instead.
I think this would return the same results.
select category,
COUNT(category),
DATENAME(Month, OpenDate) as mopendate
from myCTE
group by Category, DATENAME(Month, OpenDate), MONTH(OpenDate)
order by month(OpenDate)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2013 at 11:02 am
That did give me the same information with a lot less typing. Thank you.
May 23, 2013 at 12:41 pm
You're welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2013 at 12:50 pm
I would make one more change, in the WHERE clause:
SELECT
Category,
COUNT(Category) AS CatCount,
DATENAME(month, OpenDate) AS mopendate
FROM
Cloud_Tickets
WHERE
--(YEAR(OpenDate) = '2013')
OpenDate >= '20130101' AND
OpenDate < '20140101'
GROUP BY
MONTH(OpenDate),
Category
ORDER BY
MONTH(OpenDate);
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy