Search code examples
sqlsql-serversql-order-by

How to display months sorted in order in SQL Server?

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:

enter image description here

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 ?

Solution

  • 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.