CODEWITHSUNDEEP
sqlsql-servert-sql
neophyte
neophyte

Reputation: 1826

SQL Server ORDER BY when using DATENAME function

SELECT CAST(DATEPART(yyyy,DATE1) 
AS varchar (5)) + '-' + CAST(DATENAME(m,ec2.DATE2) 
AS varchar(3)) AS [Month],
CAST(AVG(CAST(DATEDIFF(day,DATE1, DATE2) 
AS DECIMAL(10,2))) AS DECIMAL(10,2)) AS tt
FROM tbl3
GROUP BY CAST(DATEPART(yyyy,DATE1) AS 
varchar (5)) + '-' + CAST(DATENAME(m,ec2.DATE2) 
AS varchar(3))
ORDER BY [Month]

So in the above query I have results for 2 different years so its sorting year in integer wise but Month according to alphabets(as its DATENAME) Examples: 

2013-Dec 4.45
2013-Nov 5.55
2014-Jan 2.35
2014-Jan 2-85

The problem is I am using DATE2 in my aggregate function(Avg) and hence can't use it in ORDER BY clause.

Anyone has a solution for this problem. I will be really thankful if you can help me. I read other questions regarding this problem but didn't find the solution.

Thanks in advance.

Upvotes: 1

Views: 963

Answers (2)

EarlOfEnnui
EarlOfEnnui

Reputation: 555

I agree you can order by Year/Month as Darius X suggested. You can also use a cross apply to "wrap" your calculation. Not sure it would solve your specfic problem, but for additional information. I figure out the MatchStatus in the CROSS APPLY then use MatchStatus in the SELECT & GROUP BY clauses.

SELECT
    MatchStatus,
    COUNT(*)
FROM MPI.ACTData.Matches
    CROSS APPLY (SELECT CASE WHEN MatchScore >= 69 THEN 'Match' 
                ELSE 'Review' END MatchStatus) ms
GROUP BY MatchStatus

Upvotes: 0

user353gre3
user353gre3

Reputation: 2755

A convoluted way, but works in any situation. Use CTE to solve your issue as below:

With CTE as (
SELECT CAST(DATEPART(yyyy,DATE1) 
AS varchar (5)) + '-' + CAST(DATENAME(m,ec2.DATE2) 
AS varchar(3)) AS [Month],
CAST(AVG(CAST(DATEDIFF(day,DATE1, DATE2) 
AS DECIMAL(10,2))) AS DECIMAL(10,2)) AS tt
FROM tbl3
GROUP BY CAST(DATEPART(yyyy,DATE1) AS 
varchar (5)) + '-' + CAST(DATENAME(m,ec2.DATE2) 
AS varchar(3))
Select * from CTE
ORDER BY 
DATEPART(yyyy, cast([Month] as datetime)),
DATEPART(mm, cast([Month] as datetime));

Upvotes: 2

Related Questions