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?
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
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)
The number to the right of the month abbreviation.... does that represent year?
If so....
Code:
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
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.