Samooramad
asked on
Order by for Month Names
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,[SubmissionDat e]) as MonthName
,DATEPART(YEAR,[Submission Date]) 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].[ILicensesRene wApplicati ons]R
WHERE StatusID <> 1
Group by DATENAME(MM,[SubmissionDat e]),DATEPA RT(YEAR,[S ubmissionD ate])
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].[ILicensesRene wApplicati ons]R
WHERE StatusID <> 1
order by MonthName, YearName
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,[SubmissionDat
,DATEPART(YEAR,[Submission
,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].[ILicensesRene
WHERE StatusID <> 1
Group by DATENAME(MM,[SubmissionDat
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].[ILicensesRene
WHERE StatusID <> 1
order by MonthName, YearName
ASKER
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.
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.
Just tested something similar, which should work when applied to your query
Loaded a table of dates:
This query produced the same error:
However, modify it like this and it works just fine:
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.
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')
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])
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])
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
sjwales,
sorry I'm lost. Could you explain how I need to modify my query in order to avoid the error?
sorry I'm lost. Could you explain how I need to modify my query in order to avoid the error?
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:
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.
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])
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,[Submission Date]) 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].[ILicensesRene wApplicati ons]
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])
CASE WHEN SubmissionDate IS NULL THEN 'Sum' ELSE DATENAME(MONTH,[Submission
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].[ILicensesRene
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(MonthNam e + ' 1, 1900' AS DATETIME)) " instead of "monthname" in your order by clause try below query might helps you...
SELECT DATENAME(MM,[SubmissionDat e]) as MonthName
,DATEPART(YEAR,[Submission Date]) 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].[ILicensesRene wApplicati ons]R
WHERE StatusID <> 1
Group by DATENAME(MM,[SubmissionDat e]),DATEPA RT(YEAR,[S ubmissionD ate])
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].[ILicensesRene wApplicati ons]R
WHERE StatusID <> 1
order by DATEPART(mm,CAST(MonthName + ' 1, 1900' AS DATETIME)) , YearName
"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(MonthNam
SELECT DATENAME(MM,[SubmissionDat
,DATEPART(YEAR,[Submission
,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].[ILicensesRene
WHERE StatusID <> 1
Group by DATENAME(MM,[SubmissionDat
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].[ILicensesRene
WHERE StatusID <> 1
order by DATEPART(mm,CAST(MonthName
ASKER
sjwales,
sorry for delay. I tried your posted solution and still get errors:
Msg 8120, Level 16, State 1, Line 9
Column 'STR.dbo.ILicensesRenewApp lications. Submission Date' 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'.
sorry for delay. I tried your posted solution and still get errors:
Msg 8120, Level 16, State 1, Line 9
Column 'STR.dbo.ILicensesRenewApp
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'.
ASKER
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.
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.
ASKER
My first post had errors, the second one works fine.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that worked great! thank you
ASKER
Great
So I guess you never tried my query?
ASKER
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
I was going through the solutions in the order they were posted.
Then I guess you overlooked my comment here http:#a38731853:
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)
ASKER
Didn't "overlook" it. The solution posted right before your was the first one that worked for me.
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:
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)
ASKER
No, I missed that and was checking ewangoya's response to my question. Thank you for your posts!
order by MonthName, YearName
to:
order by DATEPART(MM,[SubmissionDat
That should do the trick (tested on SQL Server 2005)