ordering by month 1

[mjd3000]

I have a field that is populated with monthly periods such as Apr-09, May-09, Jun-09 and then will continue with Jan-10 etc. Is there any way to order them in the correct date order?

 

[PWise]

if the month - year field is a text field what i would do is
create a table monthtable with 2 fields
monthvalue number
monthtext text
monthvalue ,monthtext
1 jan
2 feb
....

inner join monthtable on left(monthperiods,3)=monthtext
order by val(right(monthperiods,2),monthvalue

 

[mjd3000]

This doesn't work because when I try to order the month periods I have to select distinct from the table as I am getting the values from multiple rows.

This SQL gives me the error that 'Order By items must appear in the Select list if Select Distinct is used'

select distinct e.new_claimperiodidname
from filterednew_evidence e
inner join monthtable m on left(e.new_claimperiodidname,3) = m.monthtext
order by right(e.new_claimperiodidname,2)

 

[gmmastros]

The number to the right of the month abbreviation.... does that represent year?

If so....

Select new_claimperiodidname
From   (
       select distinct e.new_claimperiodidname
       from filterednew_evidence e
       inner join monthtable m on left(e.new_claimperiodidname,3) = m.monthtext
       ) As AliasName
order by Convert(DateTime, '01-' + new_claimperiodidname)

This code uses a derived table to prevent the error you were getting ('Order By items must appear in the Select list if Select Distinct is used').

Then, it adds 01- in front of your dates (to make it the first day of the month) and then sorts you data as though it were an actual date.

The only problem you may run in to may be bad data. This would occur if the data in the new_claimperiodidname column cannot be converted to a date after prepending it with 01.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mjd3000]

Thanks George!