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