CODEWITHSUNDEEP
sqlsql-server
Jay
Jay

Reputation: 3030

SELECT MAX date difference after having calculated the date difference

I am picking up some essential SQL query skills, and working with dates. I was successful in getting the following.

SELECT
    [FirstName],
    [LastName],
    [JobTitle],
    [JoiningDate]
    DATEDIFF(day, StartDate, '2021/03/20') AS DateDifference
FROM 
    [GenericITCompany].[dbo].[Employees]
WHERE 
    JobTitle = 'UnityDeveloper';

This returns the number of days, every developer from the Unity team, has been employed with the company.

Now, I wish to find out who has been with the company the longest.

I tried this:

SELECT
    [FirstName],
    [LastName],
    [JobTitle],
    [JoiningDate]
    DATEDIFF(day, StartDate, '2021/03/20') AS DateDifference
    MAX(DateDifference) AS LongestServingEmployee
FROM 
    [GenericITCompany].[dbo].[Employees]
WHERE 
    JobTitle = 'UnityDeveloper';

That is not working. I am possibly missing something very obvious.

Note1 : I understand basic usage of Max. For example,

SELECT 
    MAX(StartDate) AS MaximumStartDate
FROM
    [GenericITCompany].[dbo].[Employees]

But, I am primarily having challenges applying MAX to a query generated table. I believe that is my main problem.

Note2 : I have looked at some existing questions with similar issues

Fetch the row which has the Max value for a column

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

But I am not able to understand from them.

Upvotes: 0

Views: 2061

Answers (3)

Stu
Stu

Reputation: 32579

I'm not clear on what you want to see as a result, do you want to see just the employee(s) with the longest serving date (there may be ties?)

Or all the employees with the largest number of days on every row - that would seem to be what your query is trying to do, in which case you can do the following, and order by the LongestServiceEmployee. I've also used GETDATE() in place of your hard coded date

SELECT
    [FirstName],
    [LastName],
    [JobTitle],
    [JoiningDate]
    DATEDIFF(day, createdate, GETDATE()) AS DateDifference
    MAX(day, createdate, GETDATE()) over() AS LongestServingEmployee
FROM [dbo].[Employees]
WHERE JobTitle = 'UnityDeveloper'
order by LongestServingEmployee desc;

If you wanted to just find the employee(s) with the max value then wrap the query in an outer select and filter:

SELECT* FROM (
  SELECT
    [FirstName],
    [LastName],
    [JobTitle],
    [JoiningDate]
    DATEDIFF(day, createdate, GETDATE()) AS DateDifference
    MAX(day, createdate, GETDATE()) over() AS LongestServingEmployee
  FROM [dbo].[Employees]
  WHERE JobTitle = 'UnityDeveloper'
)e
WHERE Datedifference=LongestServingEmployee

Upvotes: 1

Annamalai D
Annamalai D

Reputation: 899

You cannot use DateDifference column name in the same query


SELECT
[FirstName],
[LastName],
[JobTitle],
[JoiningDate],
DATEDIFF(day, StartDate, '2021/03/20') AS DateDifference
INTO #Employees
FROM [GenericITCompany].[dbo].[Employees]
WHERE JobTitle='UnityDeveloper';

Select  *
From #Employees where DateDiffernce = (Select Max(DateDiffernce) From #Employees)

Upvotes: 0

etsuhisa
etsuhisa

Reputation: 1758

You can use like the following subquery with the MIN function to get the smallest date instead of the date difference.

SELECT
[FirstName],
[LastName],
[JobTitle],
[JoiningDate]
DATEDIFF(day, StartDate, '2021/03/20') AS DateDifference
FROM [GenericITCompany].[dbo].[Employees]
WHERE JobTitle='UnityDeveloper'
-- Getting the smallest StartDate
AND StartDate = (
  SELECT MIN(StartDate) AS MaximumStartDate
  FROM [GenericITCompany].[dbo].[Employees]
)

Upvotes: 1

Related Questions