CODEWITHSUNDEEP
sqlsql-serverdatesql-order-by
Taz
Taz

Reputation: 169

Combining Dates and Ordering by Date

I have the below Query

SELECT distinct 
COUNT(Status) AS [Transactions], 
left(DATENAME(mm, Date_Reported), 3) AS Month, 
DATENAME(yyyy, Date_Reported) AS Year

FROM [Transactions]

GROUP BY DATENAME(mm, Date_Reported), DATENAME(yyyy,Date_Reported)

ORDER BY Year, Month DESC;

My output is as below:

Transaction | Month | Year

123         | Jan   | 2000

1234        | Mar   | 2000

12          | Feb   | 2000

How can I alter the query so I can get the month and year together like "Jan 2000" and then order it by the date so Jan 2000, Feb 2000 and Mar 2000

Thank you in advance

Upvotes: 1

Views: 45

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I think you want :

SELECT COUNT(Status) AS [Transactions], t1.MonthYear
FROM [Transactions] t
CROSS APPLY ( VALUES (CONCAT(DATENAME(mm, Date_Reported),' ',
                             DATENAME(yyyy, Date_Reported)),
                      DATEPART(mm, Date_Reported)
                     )
            ) t1 (MonthYear, Morder)
GROUP BY t1.MonthYear, t1.Morder
ORDER BY t1.Morder;

Upvotes: 1

Related Questions