Order By Not working for Quarter Years

Question

Hi All,

Expected

[care session quarter]
Q3-14
Q4-14
Q1-15
Q2-15
Q3-15

Currently the output am getting shown below

care session quarter
Q1-15
Q2-15
Q3-14
Q3-15
Q4-14

Any solutions please suggest. am using this [care session quarter] column in the group by clause to achieve but no success.IF I use date column in  the select clause and Group by clause then it comes correctly but groups by all dates which is not required.

Ideally I want show only quarter aggregates. The [Date Dimension] table has the column [care session quarter]  which stores all the quarters of years along with dates for each day. i..e I have all columns in [Date Dimesion] table as shown below

Column_name
DATE_KEY
temp_date
DATE_NAME
DATE_WEEKDAY_NAME
DATE_IS_WORKDAY
DATE_NUMBER_IN_WEEK
DATE_NUMBER_IN_MONTH
DATE_NUMBER_IN_YEAR
WEEK_KEY
WEEK_NAME
WEEK_NUMBER_IN_YEAR
MONTH_KEY
MONTH_NUMBER
MONTH_NAME
MONTH_NUMBER_IN_YEAR
FY_MONTH_KEY
FY_MONTH_NUMBER
FY_MONTH_NAME
FY_MONTH_NUMBER_IN_YEAR
QUARTER_KEY
QUARTER_NUMBER
FY_QUARTER_NUMBER
CARE SESSION QUARTER
FY_QUARTER_NAME
YEAR_KEY
YEAR_NAME

Could any one help me out the order by problem

Thanks,

Mahesh

---

[e-mail]

Answer 1

Hello,

"CARE SESSION QUARTER" is text and so it's ordered as text; the result is correct so far, but not as you want it.

Order first by "YEAR_KEY" and then by "QUARTER_KEY" to get the expected result.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hi Mahesh,

After testing the issue in my environment, we can use the following query to achieve your requirement:

select [care session quarter] 
from [Date Dimesion]
order by right([care session quarter],2),
left([care session quarter],2)

Reference:
ORDER BY Clause (Transact-SQL)

Thanks,
Katherine Xiong

---

Katherine Xiong
TechNet Community Support