CODEWITHSUNDEEP
sqlsql-serversql-order-by
pro_data
pro_data

Reputation: 161

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 ?

Upvotes: 0

Views: 226

Answers (2)

Charlieface
Charlieface

Reputation: 72501

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

Upvotes: 2

D-Shih
D-Shih

Reputation: 46249

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.

Upvotes: 2

Related Questions