The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
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 *
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
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])
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
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)