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 datename(MM, date) as month_name
from tab2
union
select datename(MM, date) as month_name
from tab2
order by datepart(mm, [date])
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 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 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 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])
Try it this way (notice how I also reversed the order of the year and month)
Try it this way (notice how I also reversed the order of the year and month)
order by MonthName, YearName
to:
order by DATEPART(MM,[SubmissionDat
That should do the trick (tested on SQL Server 2005)