IS There a Way to Output Months Ordered By Calendar Year?

[VAST39]

Hi everyone,

I built a query that takes a person's date, and performs a count for each month.

Here's part of the query:

SELECT A.MONTHS,
       A.YEAR,
       COUNT(*)

FROM 



(select A.SSN, 
 A.YEAR,

   CASE


   when A.MONTH = 1 then 'JANUARY'
   when A.MONTH = 2 then 'FEBUARY'
   when A.MONTH = 3 THEN 'MARCH'
   WHEN A.MONTH = 4 THEN 'APRIL'
   WHEN A.MONTH = 5 THEN 'MAY'
   WHEN A.MONTH = 6 THEN 'JUNE'
   WHEN A.MONTH = 7 THEN 'JULY'
   WHEN A.MONTH = 8 THEN 'AUGUST'
   WHEN A.MONTH = 9 THEN 'SEPTEMBER'
   WHEN A.MONTH = 10 THEN 'OCTOBER'
   WHEN A.MONTH = 11 THEN 'NOVEMBER'
   WHEN A.MONTH = 12 THEN 'DECEMBER'
   
   end as MONTHS

FROM



(SELECT   distinct ssn,
         month(recip_retir_dt) as MONTH,
         year(recip_retir_dt) as YEAR ) as A

group by A.MONTHS

The query results is showing my months in alphabetical order. I want them sorted by calendar year so January would be the first.

Is there a way to do this?

 

[imex]

Hi,

Try:

SELECT 
    A.MONTH,
    CASE
        when A.MONTH = 1 then 'JANUARY'
        when A.MONTH = 2 then 'FEBUARY'
        when A.MONTH = 3 THEN 'MARCH'
        WHEN A.MONTH = 4 THEN 'APRIL'
        WHEN A.MONTH = 5 THEN 'MAY'
        WHEN A.MONTH = 6 THEN 'JUNE'
        WHEN A.MONTH = 7 THEN 'JULY'
        WHEN A.MONTH = 8 THEN 'AUGUST'
        WHEN A.MONTH = 9 THEN 'SEPTEMBER'
        WHEN A.MONTH = 10 THEN 'OCTOBER'
        WHEN A.MONTH = 11 THEN 'NOVEMBER'
        WHEN A.MONTH = 12 THEN 'DECEMBER'
    end as MONTHS,
    A.YEAR,
    COUNT(*)

FROM 

(
    SELECT distinct 
        ssn,
        month(recip_retir_dt) as MONTH,
        year(recip_retir_dt) as YEAR
    FROM MyTable
) as A

group by A.MONTH, A.YEAR
order by A.YEAR, A.MONTH

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[drlex]

Yes, if the sort field is also in the 'GROUP BY' clause.

Working on simplifying your query and using DATENAME instead of your conditional clause.

SELECT DATENAME(month,A.ym_retir),
       DATEPART(year,A.ym_retir),
       COUNT(*)
FROM
(SELECT distinct ssn,
         dateadd(mm,datediff(mm,0,(recip_retir_dt)),0) as ym_retir FROM cte) as A
group by DATENAME(month,A.ym_retir),
       DATEPART(year,A.ym_retir), A.ym_retir
order by A.ym_retir

The code "dateadd(mm,datediff(mm,0,(recip_retir_dt)),0)" should strip out the day from a date, leaving all as the first of the month; you can then group and sort by this field, but omit it from the result set.

soi là, soi carré

 

[drlex]

P.S. In the code, substitute your source table for the "cte" reference. I used a table called "cte" as a quick test of the code provided.

soi là, soi carré