Colin Colin - 3 months ago 13
SQL Question

After reducing an SQL date down to MMM-yyyy using format how can I then order by Month

I am trying to group some aggregate numbers into months. The values stored are full datetime. I have successfully done this using the below statements but I can no longer order by date (instead it uses August as the first in the list as it begins with A).
My question is how can I now make the field RevDate either a Date field, or at least order correctly the months?

SELECT
tbl1.AName
, tbl1.AStore
, tbl1.AVr
,Count ( tbl2.RevTag1) as Tag1
,Count ( tbl2.RevTag2) as Tag2
,Count ( tbl2.RevTag3) as Tag3
,FORMAT ( tbl2.RevDateTime,'MMM-yyyy') As 'RevDate'

FROM
tbl2
INNER JOIN tbl1
ON tbl2.AppID = tbl1.AppID
Group by tbl1.AName, tbl1.AStore, tbl1.AVr, FORMAT ( tbl2.RevDateTime,'MMM-yyyy')
Order by FORMAT ( tbl2.RevDateTime,'MMM-yyyy') asc


thanks in advance

Answer

This should work:

SELECT  tbl1.AName, 
        tbl1.AStore, 
        tbl1.AVr,
        COUNT( tbl2.RevTag1) as Tag1,
        COUNT( tbl2.RevTag2) as Tag2,
        COUNT( tbl2.RevTag3) as Tag3,
        FORMAT(tbl2.RevDateTime,'MMM-yyyy') As RevDate
FROM tbl2
INNER JOIN tbl1
    ON  tbl2.AppID =  tbl1.AppID
GROUP BY tbl1.AName, 
         tbl1.AStore, 
         tbl1.AVr, 
         FORMAT( tbl2.RevDateTime,'MMM-yyyy'),
         CONVERT(VARCHAR(6),tbl2.RevDateTime,112)
ORDER BY CONVERT(VARCHAR(6),tbl2.RevDateTime,112) asc;