• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

How to sort this kind of data containing month names

Table - mstm1
contains fields month and year
year may contain 5 months or 2 months for any year ie. varied no, of months.

If year 2009 has 3 distinct months as April June September
The output should come as All Months
                                            April
                                            June
                                            September

Similarly if year 2010 has all months
then the output should be
All Months
January
February
March
and so on
0
searchsanjaysharma
Asked:
searchsanjaysharma
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
Date and Time columns carry the information as numbers so they will sort properly and you can do date-time arithmetic on the data.  I suggest that you add a column that contains the month as a number or even the year-month like '201001' for January 2010.  Then you can ORDER BY the year and month number.  If you return the text version also, you can use it for display.
0
 
searchsanjaysharmaAuthor Commented:
No i have two fields seprately, year in bigint and month as monthname not month number.
0
 
Dave BaldwinFixer of ProblemsCommented:
I understand that.  You can't sort 'monthname' properly so I suggest adding a month number column.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Ephraim WangoyaCommented:
Two approaches I can think of
1.

select *
 from yourtable
order by DATEPART(mm, CAST(MonthField + ' 1, ' + cast(YearField as varchar(4)) AS DATETIME))

Open in new window

0
 
Ephraim WangoyaCommented:
2.
 
 
select * 
from yourtable
order by 
	YearField, 
	CASE MonthField
	   WHEN 'January' THEN 1
	   WHEN 'February' THEN 2
	   WHEN 'March' THEN 3
	   WHEN 'April' THEN 4
	   WHEN 'May' THEN 5
	   WHEN 'June' THEN 6
	   WHEN 'July' THEN 7
	   WHEN 'August' THEN 8
	   WHEN 'September' THEN 9
	   WHEN 'October' THEN 10
	   WHEN 'November' THEN 11
	   WHEN 'December' THEN 12
	END

Open in new window

0
 
Scott PletcherSenior DBACommented:
ORDER BY
    CHARINDEX (MonthField, 'JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember')
0
 
searchsanjaysharmaAuthor Commented:
Thanx
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now