CODEWITHSUNDEEP
sqlsql-serversortingsql-order-by
Amrit Singh
Amrit Singh

Reputation: 63

How to Sort the Financial Year quarters correctly in SQL?

I use the following code to change the date for respective quarter.

    CASE
        WHEN MONTH(B.CDate) BETWEEN 1  AND 3  THEN 'Q3 ' + convert(char(4), YEAR(B.CDate) - 0)
        WHEN MONTH(B.CDate) BETWEEN 4  AND 6  THEN 'Q4 ' + convert(char(4), YEAR(B.CDate) - 0)
        WHEN MONTH(B.CDate) BETWEEN 7  AND 9  THEN 'Q1 ' + convert(char(4), YEAR(B.CDate) + 1)
        WHEN MONTH(B.CDate) BETWEEN 10 AND 12 THEN 'Q2 ' + convert(char(4), YEAR(B.CDate) + 1) 
    END AS Quarter,

How to I correctly order on Quarter

Upvotes: 0

Views: 1218

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

Assuming this is an aggregation query, you can just choose a date in the quarter:

order by min(b.CDate)

You could sort directly by quarter if you represented them as YYYYQn instead of QnYYYY.

A CASE expression is unnecessary. You can also simplify the logic to:

CONCAT(DATENAME(YEAR, DATEADD(MONTH, -6, B.CDate)),
       'Q', DATENAME(QUARTER, DATEADD(MONTH, -6, B.CDate))
      ) as Quarter,

That is, your quarter is the "standard" quarter you get when you subtract 6 months.

Upvotes: 1

Related Questions