CODEWITHSUNDEEP
mysqlsql
LearningProcess
LearningProcess

Reputation: 613

Select longest working employee in SQL

My database looks like:

Table: dept_emp:

+--------------------------+-----------------------+------------------------+
| emp_no (Employee Number) | from_date (Hire date) | to_date (Worked up to) |
+--------------------------+-----------------------+------------------------+
|                        5 | 1995-02-27            | 2001-01-19             |
|                      500 | 1968-01-01            | 9999-01-01             |
+--------------------------+-----------------------+------------------------+

Note: If the employee is still currently working for the company their to_date will show 9999-01-01.

What I'm wanting to do is display the emp_no of the longest working employee. I'm not sure how to do that with the random 9999-01-01's in the database.

Here's what I've come up with so far:

SELECT emp_no 
  FROM (SELECT max(datediff( (SELECT to_date 
                                FROM dept_emp), 
                             (SELECT from_date 
                                FROM dept_emp)
                           )
                  )
       );

This doesn't work, and it also doesn't the take 9999-01-01 into account.

I'm thinking I should use CURDATE() in their some where?

Upvotes: 0

Views: 4901

Answers (5)

PeterJThaggard
PeterJThaggard

Reputation: 13

Assuming you want the longest-tenured worker who is still employed (i.e., their to_date = '9999-01-01'), then this should work:

select emp_no 
from dept_emp 
where to_date = '9999-01-01' and 
datediff(to_date, from_date) = 
( 
   select max(datediff(to_date, from_date)) 
   from dept_emp where to_date = '9999-01-01'
);

Upvotes: 0

quasoft
quasoft

Reputation: 5438

If the number of employees is not large, there is a simple solution:

SELECT
  *, datediff(to_date, from_date) AS duration
FROM (
  SELECT
    emp_no, from_date, IF(to_date <> '9999-01-01', to_date, CURRENT_DATE) AS to_date
  FROM
    dept_emp
) AS tmp
ORDER BY duration DESC
LIMIT 1

This uses a subselect to replace the '9999-01-01' value with the CURRENT_DATE, then, it orders results by duration, and get the first employee. Again, this is efficient enough if the number of rows in the table is not too large.

Here is a SQL Fiddle.

Sample data:

INSERT INTO dept_emp VALUES
(500, '1968-01-01', '2016-01-05'),
(650, '1970-01-01', '9999-01-01'),
(700, '2006-01-01', '2016-01-01');

Result:

| emp_no |                 from_date |                   to_date | duration |
|--------|---------------------------|---------------------------|----------|
|    500 | January, 01 1968 00:00:00 | January, 05 2016 00:00:00 |    17536 |

And here is official MySQL documentation on control flow functions:

Upvotes: 0

Muhammed
Muhammed

Reputation: 1612

Firstly I would suggest to make that to_date DEFAULT NULL. You want to have NULL there, if employee is still working, no need for 9999- stuff.

Now, to your question about longest working employee. You could calculate the date difference like this, accounting for NULL to be today's date:

SELECT emp_no, MAX(DATEDIFF( IFNULL(to_date,CURDATE()) ,from_date)) FROM dept_emp;

Here what we did, is if to_date is NULL, meaning person is still employed, we assume his to_date is today's date which is true.

EDIT: I am sorry, forgot to return the employee number, just add your emp_no to the query.

EDIT 2: Since you are not allowed to use NULL, this is what you should do:

  SELECT emp_no, MAX(DATEDIFF( IF(to_date='9999-01-01',CURDATE(), to_date) ,from_date)) FROM dept_emp;

So basically, we are saying if 9999- is set, use it as todays date. Hope this helps. I assume that no one's to_date is not going to be bigger that today's date, other than 9999- of course.

EDIT 3: You are right about emp_no, so here it goes:

SELECT emp_no, DATEDIFF( IF(to_date='9999-01-01',CURDATE(), to_date) 
,from_date) as longest_date FROM dept_emp ORDER BY longest_date DESC LIMIT 0,1;

Upvotes: 1

jkdba
jkdba

Reputation: 2519

This will return the employee id that has worked the longest. Note the CTE is not necessary its purely for readability. See second query if you are going for performance. You can solve the '9999-01-01' max date issue by using a Case statement like so.

With CTE:

;with cte_stage as (
    select emp_no
    ,case when to_date = '9999-01-01' 
         then DateDiff(DAY,from_date,GETDATE()) 
         else DATEDIFF(DAY,from_date,to_date) END as 'age'
     from dept_emp
)
select emp_no
from cte_stage
where age = ( select max(age) from cte_stage)

Without CTE:

select emp_no
from dept_emp
where case 
    when to_date = '9999-01-01' 
    then DateDiff(DAY,from_date,GETDATE()) 
    else DATEDIFF(DAY,from_date,to_date) END  = 
    ( 
         select max( 
              case 
                   when to_date = '9999-01-01' 
                   then DateDiff(DAY,from_date,GETDATE()) 
                   else DATEDIFF(DAY,from_date,to_date) 
               END 
          ) 
    from dept_emp)

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37109

You could try something like this:

select 
  d.*,
  datediff(
    case when to_date = '9999-01-01' then current_date else to_date end,
    from_date) as how_long
from dept_emp d
where 
datediff(
    case when to_date = '9999-01-01' then current_date else to_date end,
    from_date) = (

  -- find the longest tenure
  select max(datediff(
    case when to_date = '9999-01-01' then current_date else to_date end,
    from_date))
  from dept_emp

)

If this is the kind of information you have in your table:

create table dept_emp (
  emp_no int,
  from_date date,
  to_date date
);
insert into dept_emp values 
(1, '2000-01-01', '2000-01-02'),
(2, '2000-01-01', '2005-02-01'),
(3, '2000-01-01', '9999-01-01');

Your result will be:

| emp_no |                 from_date |                   to_date | how_long |
|--------|---------------------------|---------------------------|----------|
|      3 | January, 01 2000 00:00:00 | January, 01 9999 00:00:00 |     5902 |

Example SQLFiddle: http://sqlfiddle.com/#!9/55886/11

Upvotes: 1

Related Questions