Order by latest Month + Year

1

Hey guys,

I have this Code here which I can't get to ORDER by correctly. It's probably a really easy fix but I can't seem to get it.

Select Top 1000000 DateName(mm, htblticket.date) As Month,
  Count(htblticket.ticketid) As Tickets
From htblticket
Where htblticket.spam <> 'True'
Group By DateName(yyyy, htblticket.date),
  DateName(mm, htblticket.date)
Order By DateName(yyyy, htblticket.date) Desc,
  Month Desc

I want it to sort with the latest Month + latest Year at the top.

Kind regards,
Patrick

2

You are ordering it by the first column Month. So "August" will be one of the first months instead of month number 8. Not sure if you are using SQL server but this will be a nice column

LEFT(CONVERT(varchar, htblticket.date,112),6)

You should use

...
GROUP BY 
DateName(yyyy, htblticket.date),
DateName(mm, htblticket.date),
MONTH(htblticket.date)
ORDER BY 
DateName(yyyy, htblticket.date) Desc,
MONTH(htblticket.date) Desc
1 Like
3

Here's a slightly different way. Things get easier (and faster) when you separate the data layer from the display layer. You can change it to display any way you want to in the outer Select without having to touch the GROUP BY.

   WITH ctePreAgg AS
(--==== Create the totals based on the "Month Serial Number" (This is the "Data Layer"
 SELECT  MonthSerial#   = DATEDIFF(mm,0,Date) --Number of months since 19000101
        ,Tickets        = COUNT(*)
   FROM dbo.htblticket
  GROUP BY DATEDIFF(mm,0,Date)
)--==== This sorts and displays the data like you want. (This is the "Presentation Layer")
 SELECT  [Month] = RIGHT(CONVERT(CHAR(11),DATEADD(mm,cte.MonthSerial#,0),106),8)
        ,Tickets
   FROM ctePreAgg cte
  ORDER BY MonthSerial# DESC --See how nice it sorts for us regardless of display method?
;

Partial Results...

image
image281×770 10.6 KB

If the month of the year are NOT in the English Language, post back... there's a pretty easy fix for that.

If you want to display a "0" for months were there were no tickets, we'll need to gen the months and left join to them but that's up to you to let us know.

4

Hi there,

that below portion (GROUP BY & ORDER BY) fixed it for me, thank you very much! :slight_smile: