SELECT MAX date difference after having calculated the date difference

Question

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.

Do NOT develop a habit of using 3 part names for your tables. That will make your code very difficult to migrate to different environments - something that is frequently done as code moves from development to test to production. Your connection should determine the database to use for your object (i.e., table) references. — SMor, Mar 20 '21 at 12:55
Sometimes we over-complicate things. You don't need to calculate anything to answer "who has been with the company the longest". That is simply the person with the earliest (i.e., minimum) StartDate - no need to use DATEDIFF for that. For this, you could use TOP 1 with ORDER BY STARTDATE. — SMor, Mar 20 '21 at 12:58
@SMor I agree. I am used to using Entity Framework in .NET where I dont have to write direct SQL queries. Recently, I was forced to use SQL directly for a client project. I will definitely include your advice in my future SQL usage. — Jay, Mar 20 '21 at 13:59

score 1 · Accepted Answer · answered Mar 20 '21 at 06:22

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]
)

this is what I was looking for. it works. – Jay Mar 20 '21 at 17:49 — Jay, Mar 20 '21 at 17:49

Stu · Answer 2 · 2021-03-20T14:11:03.870

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

Yes, that is correct. I wish to do two things (even if it is long winded). First, Collect all the days of employment, then, from that, show the person with the highest number of days. — Jay, Mar 20 '21 at 14:01

Annamalai D · Answer 3 · 2021-03-20T06:41:53.367

0

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)

edited Mar 20 '21 at 06:41

answered Mar 20 '21 at 06:27

Annamalai D

859
1
7
21

Why a temp table, you can do this in one query – Charlieface Mar 20 '21 at 20:53

SELECT MAX date difference after having calculated the date difference

3 Answers3