Order by for Month Names

Change your

order by MonthName, YearName

to:

order by DATEPART(MM,[SubmissionDate]), YearName

That should do the trick (tested on SQL Server 2005)

Just tested something similar, which should work when applied to your query

Loaded a table of dates:

create table tab2 (date datetime)

insert into tab2 values ('20120101')
insert into tab2 values ('20120201')
insert into tab2 values ('20120301')
insert into tab2 values ('20120401')
insert into tab2 values ('20120501')
insert into tab2 values ('20120601')
insert into tab2 values ('20120701')
insert into tab2 values ('20120801')
insert into tab2 values ('20120901')
insert into tab2 values ('20121001')
insert into tab2 values ('20121101')
insert into tab2 values ('20121201')

Select all Open in new window

This query produced the same error:

select datename(MM, date) as month_name 
from tab2 
union
select datename(MM, date) as month_name 
from tab2
order by datepart(mm, [date])

Select all Open in new window

However, modify it like this and it works just fine:

select datename(MM, month_name) as month_name 
from
(
select date as month_name 
from tab2 
union
select date as month_name 
from tab2 ) as a
order by datepart(mm, [month_name])

Select all Open in new window

Apply the same concept to your query and it should work.

Inner query with the union just lists the date, then select from the inner query, do the datename/datepart functions and apply the order by to that.

for simple query you can Add following in Order by clause
MONTH(SubmissionDate)

and for your query, justy add month Number in your query and then then order by that column

You can order using a case statement

SELECT  DATENAME(MM,[SubmissionDate]) as  MonthName
			,DATEPART(YEAR,[SubmissionDate]) as YearName
			,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END) Riyadh
			,SUM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END)  Jeddah
			,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END)  Dammam
			,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as  Total

FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
Group by DATENAME(MM,[SubmissionDate]),DATEPART(YEAR,[SubmissionDate])
UNION 
SELECT  'Sum'  MonthName
            ,' ' YearName
            ,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END)  Riyadh
            ,suM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END)  Jeddah
            ,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END)   Dammam
            ,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as Total

FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
order by case MonthName
			when 'January' then 1
			when 'February' then 2
			when 'March' then 3
			when 'April' then 4
			when 'May' then 5
			when 'June' then 6
			when 'July' then 7
			when 'August' then 8
			when 'September' then 9
			when 'October' then 10
			when 'November' then 11
			when 'December' then 12
			else 13
		end, 
		YearName

Select all Open in new window

Try it this way (notice how I also reversed the order of the year and month):

SELECT  MonthName,
        YearName,
        Riyadh,
        Jeddah,
        Dammam,
        Total
FROM    (SELECT DATEPART(YEAR, [SubmissionDate]) AS YearName,
                DATEPART(MM, [SubmissionDate]) MonthNumber,
                DATENAME(MM, [SubmissionDate]) AS MonthName,
                SUM(CASE R.BranchID
                      WHEN 1 THEN 1
                      ELSE 0
                    END) Riyadh,
                SUM(CASE R.BranchID
                      WHEN 2 THEN 1
                      ELSE 0
                    END) Jeddah,
                SUM(CASE R.BranchID
                      WHEN 3 THEN 1
                      ELSE 0
                    END) Dammam,
                SUM(CASE WHEN R.BranchID = '1'
                              OR R.BranchID = '2'
                              OR R.BranchID = '3' THEN 1
                         ELSE 0
                    END) AS Total
         FROM   [STR].[dbo].[ILicensesRenewApplications] R
         WHERE  StatusID <> 1
         GROUP BY DATEPART(year, [SubmissionDate]),
                DATEPART(month, [SubmissionDate]),
                DATENAME(month, [SubmissionDate])
         UNION ALL
         SELECT 9999 YearName,
                13,
                'Sum' MonthName,
                SUM(CASE R.BranchID
                      WHEN 1 THEN 1
                      ELSE 0
                    END) Riyadh,
                SUM(CASE R.BranchID
                      WHEN 2 THEN 1
                      ELSE 0
                    END) Jeddah,
                SUM(CASE R.BranchID
                      WHEN 3 THEN 1
                      ELSE 0
                    END) Dammam,
                SUM(CASE WHEN R.BranchID = '1'
                              OR R.BranchID = '2'
                              OR R.BranchID = '3' THEN 1
                         ELSE 0
                    END) AS Total
         FROM   [STR].[dbo].[ILicensesRenewApplications] R
         WHERE  StatusID <> 1
        ) x
ORDER BY YearName,
        MonthNumber

Select all Open in new window

Basically what you want to do is do a select from a select.

I don't have your exact database layout to play with but something fairly close to this should work:

SELECT  DATENAME(MM,[SubmissionDate]) as  MonthName
            ,DATEPART(YEAR,[SubmissionDate]) as YearName
            ,Riyadh
            ,Jeddah
            ,Dammam
            ,Total
FROM
(
SELECT  [SubmissionDate] as  MonthName
            ,[SubmissionDate] as YearName
            ,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END) Riyadh
            ,SUM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END)  Jeddah
            ,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END)  Dammam
            ,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as  Total

FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
Group by DATENAME(MM,[SubmissionDate]),DATEPART(YEAR,[SubmissionDate])

UNION
SELECT  'Sum'  MonthName
            ,' ' YearName
            ,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END)  Riyadh
            ,suM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END)  Jeddah
            ,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END)   Dammam
            ,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as Total

FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
) as a
order by DATEPART(MM,[SubmissionDate])
            ,DATEPART(YEAR,[SubmissionDate]) 

Select all Open in new window

You have your base query as the "inner" query that does the majority of the work, executing your CASE statements and doing the UNION and filtering on the StatusID but returning the date twice (although, you could actually only return it once and then select it twice in the outer select - either way works).

That then returns a result set that you can select from again and perform your order by manipulations on.

The inner query is returning a basic date.  All you do then is select all of the results and manipulate the date returned to show you names in the query but order by their actual place in the year.

SELECT
    CASE WHEN SubmissionDate IS NULL THEN 'Sum' ELSE DATENAME(MONTH,[SubmissionDate]) END AS MonthName,
    CASE WHEN SubmissionDate IS NULL THEN ' ' ELSE DATEPART(YEAR, [SubmissionDate]) END AS YearName,
    Riyadh,
    Jeddah,
    Dammam,
    [Total]    
FROM (
      SELECT  
                         DATEADD(MONTH, DATEDIFF(MONTH, 0, [SubmissionDate]), 0) AS SubmissionDate
                        ,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END) AS Riyadh
                        ,SUM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END) AS Jeddah
                        ,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END) AS Dammam
                        ,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) AS [Total]
      FROM [STR].[dbo].[ILicensesRenewApplications]
      WHERE StatusID <> 1 AND R.BranchID IN (1, 2, 3)
      GROUP BY     DATEADD(MONTH, DATEDIFF(MONTH, 0, [SubmissionDate]), 0) WITH ROLLUP
) AS derived_table
ORDER BY
    MONTH([SubmissionDate]),
    YEAR([SubmissionDate])

you mentioned that

"how to sort by Month order instead of having months sorted alphabetically"

means you want to sort result by month number instead of monthname..

if so you must use "DATEPART(mm,CAST(MonthName + ' 1, 1900' AS DATETIME)) " instead of "monthname" in your order by clause try below query might helps you...


SELECT  DATENAME(MM,[SubmissionDate]) as  MonthName
            ,DATEPART(YEAR,[SubmissionDate]) as YearName
            ,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END) Riyadh
            ,SUM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END)  Jeddah
            ,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END)  Dammam
            ,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as  Total

FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
Group by DATENAME(MM,[SubmissionDate]),DATEPART(YEAR,[SubmissionDate])

UNION
SELECT  'Sum'  MonthName
            ,' ' YearName
            ,SUM(CASE R.BranchID WHEN 1 THEN 1 ELSE 0 END)  Riyadh
            ,suM(CASE R.BranchID WHEN 2 THEN 1 ELSE 0 END)  Jeddah
            ,SUM(CASE R.BranchID WHEN 3 THEN 1 ELSE 0 END)   Dammam
            ,SUM(CASE WHEN R.BranchID ='1' OR R.BranchID ='2' OR R.BranchID ='3' THEN 1 ELSE 0 END) as Total

FROM [STR].[dbo].[ILicensesRenewApplications]R
WHERE StatusID <> 1
order by DATEPART(mm,CAST(MonthName + ' 1, 1900' AS DATETIME)) , YearName

My first post had errors, the second one works fine.

So I guess you never tried my query?

I was going through the solutions in the order they were posted.
Then I guess you overlooked my comment here http:#a38731853:

Try it this way (notice how I also reversed the order of the year and month)

I was referring to the ORDER BY and your comment today:
your second query worked great but I have January 2013 appearing at the top. How do I get that to appear on the bottom?
I suspect you overlooked my comment:

Try it this way (notice how I also reversed the order of the year and month)