How to return the the department leader of the departmen with the worker who has served the longest?

Scenario/Problem
I have a table that stores workers ID, when they start working at a department as well as when they leave that department. This can be seen in SQL Fiddle: http://www.sqlfiddle.com/#!9/d0c982/1/0

The below code will create the table as well as insert the test data

CREATE TABLE `Worker_Department` (
  `Worker_ID` Integer NOT NULL ,
  `Department_ID` Integer NOT NULL,
  `Position` Text NOT NULL,
`Start_Date` datetime NOT NULL,
`Leave_Date` datetime
);

  INSERT INTO `Worker_Department`(`Worker_ID`,`Department_ID`,`Position`,`Start_Date`)
  VALUES
  (10,100,'Leader','1980-11-11'),
  (20,200,'Leader','1980-11-11');


  INSERT INTO
`Worker_Department`(`Worker_ID`,`Department_ID`,`Position`,`Start_Date`,`Leave_Date`)
  VALUES
  (30,200,'Administrator','1980-11-11', '2014-02-02'),
  (40,200,'Receptionist','1975-11-11', '2014-02-02');

  INSERT INTO `Worker_Department`(`Worker_ID`,`Department_ID`,`Position`,`Start_Date`)
  VALUES
  (50,300,'Administrator','2014-02-02'),

  (30,100,'Administrator','2014-02-02');

Code (SQL):

I need to write a query that will get the longest serving worker who is currently at a department(a null end date). However those with the "Leader" position are not eligible to be the longest serving. From the results of this query I will then need to find the department leader at the department the longest serving worker is currently working at.

Expected Result
Looking at the provided test data:

• Worker 40 cannot be the longest serving employee as they are no longer working for the company.
• Worker 10 and 20 cannot be the longest serving employee as they both have the leader position (However this will make them eligible to be the department leader based on who the longest serving employee is).
• Worker 30 is the longest serving employee due to there being a bigger difference between the current date and their oldest start date when compared to worker 50.
• Worker 30 is currently working at department 100. This means that Worker 10 is the department leader at the department with the current longest serving worker

The query output would be something like
| Worker_ID
|---------------
| 10

If this table was linked as a foreign key to another table, the selection could be modified to include details about that leader (name, phone, address).

Current Progress
The below query will show workers who are currently working (those without a leave date), the department they work at as well as their role at that department.

SELECT Worker_ID, Department_ID, Position FROM Worker_Department
   WHERE position != 'Leader' AND leave_date is null

Code (SQL):

The below query will return the difference between the current date and the worker's minimum start date. However this includes those who do not have a null end date(not current)

SELECT Worker_ID, DATEDIFF(Now(), Min(Start_Date)) as NowSubMin FROM Worker_Department
WHERE position != 'Leader'
GROUP BY Worker_ID

Code (SQL):

Lastly, both of these queries have been used to create the following query that is designed to return the currently working workers alongside the date difference between the current date and their first start date

SELECT Worker_ID, DATEDIFF(Now(), Min(Start_Date)) as NowSubMin FROM Worker_Department
WHERE position != 'Leader'
GROUP BY Worker_ID

HAVING Worker_ID IN (SELECT Worker_ID FROM Worker_Department
   WHERE position != 'Leader' AND leave_date is null)

Code (SQL):

 

Something like this might work:

select departmentID as department_id from table where position!='leader' and leaveDate=null order by startDate asc limit 1

"order by startDate asc", this is the part which decides oldest working worker.
leaveDate=null, this decides that worker is still working.
position!='leader', this decides that worker is not a leader.


Now give this found departmentID to another query to select the workerID of the member who owns this user.

select * from table where position='leader' and departmentID= department_id limit 1;


The first query is selecting the departmentID of the worker who is the longest working and is not a leader.

Then its giving "that found" departmentID to main query where its selecting a leader position worker.

You can also try to put those 2 queries in a single one.

select * from table where position='leader' and
departmentID=( select departmentID where position!='leader' and leaveDate=null order by startDate desc limit 1 )
limit 1;


I hope I understood your question right...

 

This did not return anything when I tested it.

If I alter the table to

CREATE TABLE `Worker_Department` (
  `Worker_ID` Integer NOT NULL ,
  `Department_ID` Integer NOT NULL,
  `Position` Text NOT NULL,
`Start_Date` datetime NOT NULL,
`Leave_Date` datetime
);

  INSERT INTO `Worker_Department`(`Worker_ID`,`Department_ID`,`Position`,`Start_Date`)
  VALUES
  (10,100,'Leader','1980-11-11'),
  (20,200,'Leader','1980-11-11');


  INSERT INTO
`Worker_Department`(`Worker_ID`,`Department_ID`,`Position`,`Start_Date`,`Leave_Date`)
  VALUES
  (30,200,'Administrator','1980-11-11', '2014-02-02'),
  (40,200,'Receptionist','1975-11-11', '2014-02-02');

  INSERT INTO `Worker_Department`(`Worker_ID`,`Department_ID`,`Position`,`Start_Date`)
  VALUES
  (50,300,'Administrator','2014-02-02'),

  (30,100,'Administrator','2014-02-02'),
  (60,200,'Administrator','1980-11-11');

Code (markup):

Running this query will show that worker 30 and 60 are both the longest serving as their Today - Original Start Date are both the highest

SELECT Worker_ID,Min(Start_Date) as 'Original Start Date', DATEDIFF(Now(), Min(Start_Date)) as 'Today Date - Original Start Date' FROM Worker_Department
WHERE position != 'Leader'
GROUP BY Worker_ID

HAVING Worker_ID IN (SELECT Worker_ID FROM Worker_Department
   WHERE position != 'Leader' AND leave_date is null)

Code (SQL):

So to get just the longest serving workers, I would need to somehow filter it so that only workers with the maximum value in the Today Date - Original Start Date column will be returned as well as the department ID of the their most recent department.

 

This query is giving Worker_ID "20" as result

select * from worker_department where
position='leader' and
Department_ID=( select Department_ID from worker_department where position!='leader' and Leave_Date is NULL order by Start_Date asc limit 1 )
limit 1


The problem in earlier query was that I was trying to select Leave_Date=null which does not works. It should be: Leave_Date IS NULL