Solved

Order by for Month Names

Posted on 2012-12-30
24
1,275 Views
Last Modified: 2013-01-02
Hi Experts,

I have the following query which works fine. I just need to figure out how to sort by Month order instead of having months sorted alphabetically. I'm sure there is a simple way to do this but I can't think of anything.

Appreciate your help

Query is:


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 MonthName, YearName



screenshot of output
0
Comment
Question by:Samooramad
  • 10
  • 4
  • 3
  • +4
24 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38730484
Change your

order by MonthName, YearName

to:

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

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

Author Comment

by:Samooramad
ID: 38730499
Already tried that. I get this error when I do that:

Msg 104, Level 16, State 1, Line 22
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38730510
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')

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])

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])

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.
0
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 38730586
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
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 38730795
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

Open in new window

0
 
LVL 32

Accepted Solution

by:
ewangoya earned 350 total points
ID: 38730815
Correction, make it a subquery

SELECT *
FROM
(
	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
) A	
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

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38731853
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

Open in new window

0
 

Author Comment

by:Samooramad
ID: 38732371
sjwales,

sorry I'm lost. Could you explain how I need to modify my query in order to avoid the error?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38732731
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]) 

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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38732748
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])
0
 
LVL 9

Expert Comment

by:keyu
ID: 38735664
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
0
 

Author Comment

by:Samooramad
ID: 38736153
sjwales,

sorry for delay. I tried your posted solution and still get errors:

Msg 8120, Level 16, State 1, Line 9
Column 'STR.dbo.ILicensesRenewApplications.SubmissionDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'SubmissionDate'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'SubmissionDate'.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'SubmissionDate'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'SubmissionDate'.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Samooramad
ID: 38736159
ewangoya,

I tried your first post and got this error:

Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'MonthName'.
Msg 104, Level 16, State 1, Line 35
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
0
 

Author Comment

by:Samooramad
ID: 38736176
ewangoya,

your second query worked great but I have January 2013 appearing at the top. How do I get that to appear on the bottom?

output
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 38736179
My first post had errors, the second one works fine.
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 350 total points
ID: 38736182
Change the order by, start with year

order by
		YearName,
          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

Open in new window

0
 

Author Comment

by:Samooramad
ID: 38736189
that worked great! thank you
0
 

Author Closing Comment

by:Samooramad
ID: 38736199
Great
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38736710
So I guess you never tried my query?
0
 

Author Comment

by:Samooramad
ID: 38736811
sorry Scott, I didn't. I was going through the solutions in the order they were posted. But thanks to you and everyone else who posted. I just went with the first working solution
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38738681
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)
0
 

Author Comment

by:Samooramad
ID: 38738850
Didn't "overlook" it. The solution posted right before your was the first one that worked for me.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38738857
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)
0
 

Author Comment

by:Samooramad
ID: 38738871
No, I missed that and was checking ewangoya's response to my question. Thank you for your posts!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now