How to display months sorted in order in SQL Server?-CodePudding

Below is the table I have created and inserted values in it:

CREATE TABLE employees_list   
(employeeID int identity(1,1),   
employeeName varchar(25))  
GO   
  
INSERT INTO employees_list VALUES ('Kevin'),('Charles')  
GO   
   
CREATE TABLE hourlyRates   
(employeeID int,   
rate int,   
rateDate date)   
  
INSERT INTO hourlyRates VALUES (1, 28, '2016-01-01'),   
                               (1, 39, '2016-02-01'),  
                               (2, 43, '2016-01-01'),  
                               (2, 57, '2016-02-01')  
  
  
CREATE TABLE workingHours   
(employeeID int,   
startdate datetime,   
enddate datetime)  
GO   
  
INSERT INTO workingHours VALUES (1, '2016-01-01 09:00', '2016-01-01 17:00'),  
                                (1, '2016-01-02 09:00', '2016-01-02 17:00'),  
                                (1, '2016-02-01 10:00', '2016-02-01 16:00'),  
                                (1, '2016-02-02 11:00', '2016-02-02 13:00'),  
                                (2, '2016-01-01 10:00', '2016-01-01 16:00'),  
                                (2, '2016-01-02 08:00', '2016-01-02 14:00'),  
                                (2, '2016-02-01 14:00', '2016-02-01 19:00'),  
                                (2, '2016-02-02 13:00', '2016-02-02 16:00')  
GO

SELECT * FROM employees_list
SELECT * FROM hourlyRates
SELECT * FROM workingHours

Then I ran a query to calculate salaries paid to Employees each month:

SELECT employeeName,DATENAME(MONTH,startdate) AS 'Month',
SUM(DATEDIFF(HOUR,startdate,enddate) * rate) AS 'Total Salary'
FROM hourlyRates,workingHours,employees_list
WHERE hourlyRates.employeeID = workingHours.employeeID
AND employees_list.employeeID = workingHours.employeeID
AND 
(hourlyRates.rateDate
 BETWEEN DATEFROMPARTS(DATEPART(YEAR, workingHours.startDate), DATEPART(MONTH,workingHours.startDate),1) 
 AND DATEFROMPARTS(DATEPART(YEAR, workingHours.endDate), DATEPART(MONTH,workingHours.endDate),1))
 GROUP BY employeeName,DATENAME(MONTH,startdate)

And I got the following output:

As you can see from the screenshot above that I got the result I wanted. But the only issue is the month is not being displayed in order.

I tried adding ORDER BY DATENAME(MONTH,startdate) and still the order of month is not being sorted. I even tried ORDER BY DATEPART(MM,startdate) but it is showing error mentioning that it is not contained in an aggregate function or GROUP BY clause.

What minor change do I need to make in my query ?

CodePudding user response：

Why add ORDER BY DATENAME(MONTH,startdate) not work

Because the ORDER depends on character instead of the month of number.

You can try to add MONTH(startdate) in ORDER BY & GROUP BY, because you might need to add non-aggregate function in GROUP BY

SELECT employeeName,DATENAME(MONTH,startdate) AS 'Month',
       SUM(DATEDIFF(HOUR,startdate,enddate) * rate) AS 'Total Salary'
FROM hourlyRates
INNER JOIN workingHours
ON  hourlyRates.employeeID = workingHours.employeeID
INNER JOIN employees_list
ON employees_list.employeeID = workingHours.employeeID
WHERE
(hourlyRates.rateDate
 BETWEEN DATEFROMPARTS(DATEPART(YEAR, workingHours.startDate), DATEPART(MONTH,workingHours.startDate),1) 
 AND DATEFROMPARTS(DATEPART(YEAR, workingHours.endDate), DATEPART(MONTH,workingHours.endDate),1))
GROUP BY employeeName,DATENAME(MONTH,startdate),MONTH(startdate)
ORDER BY MONTH(startdate)

sqlfiddle

NOTE

I would use INNER JOIN ANSI syntax instead of , which mean CROSS JOIN because JOIN syntax is generally considered more readable.

CodePudding user response：

As mentioned, ORDER BY DATENAME will sort by the textual name of the month not by the actual ordering of months.

It's best to just group and sort by EOMONTH, then you can pull out the month name from that in the SELECT

Further improvements:

Always use explicit join syntax, not old-style , comma joins.
Give tables short aliases, to make your query more readable.
Your date interval check might not be quite right, and you may need to also adjust the rate caluclation, but I don't know without further info.
A more accurate calculation would probably mean calculating part-dates.

SELECT
  e.employeeName,
  DATENAME(month, EOMONTH(wh.startdate)) AS Month,
  SUM(DATEDIFF(HOUR, wh.startdate, wh.enddate) * hr.rate) AS [Total Salary]
FROM hourlyRates hr
JOIN workingHours wh ON hr.employeeID = wh.employeeID
  AND hr.rateDate
    BETWEEN DATEFROMPARTS(YEAR(wh.startDate), MONTH(wh.startDate), 1) 
    AND DATEFROMPARTS(YEAR(wh.endDate), MONTH(wh.endDate), 1)
JOIN employees_list e ON e.employeeID = wh.employeeID
GROUP BY
  e.employeeId,
  e.employeeName,
  EOMONTH(wh.startdate)
ORDER BY
  EOMONTH(wh.startdate),
  e.employeeName;

db<>fiddle