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

Other SQL Server Topics
1

dataoutput
dataoutput1797×259 9.42 KB

from this table, I want to return the worker who has served the longest. However, anyone with the 'Leader' position is excluded from being the longest serving.

From this result, I want to get the Worker_ID of the leader(s)

SAMPLE 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');

From the provided test data, it is expected that worker 30 is the longest serving worker. This should result in worker 10 alongside any other leaders at department 100 being outputted.

Currently, I have a query that will return the current date subtract the minimum start date for each worker, except those that are a leader. This tells me how long that worker has been with the company.

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

I also have a query that will display the current workers except for the leaders

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

However, I am unsure as to how to write a query that combines the information gathered from the previous two queries to get the department leader.

2

Is the underlying db MySQL or SQL Server (or something else)?

3

MySQL 5.6. I made the tables and queries in SQL Fiddle

4

does this work for you? I'm not totally clear on what you are asking

select w.* , ldr.*
  from #Worker_Department w
  join (
		SELECT Worker_ID, max(DateDiff(day, (start_date), GetDAte())) as NowSubMin
		 FROM #Worker_Department
		WHERE position != 'Leader'
		group by Worker_ID) maxdate
	on w.Worker_ID = maxdate.Worker_ID
	and w.Leave_Date is null
  join #Worker_Department ldr
	on w.department_id = ldr.Department_ID
	and ldr.Position = 'Leader'
5

This is close to what I want. To test it, I altered the sample data so that the entry for worker 50 had a start date of 1950-02-02 and a department ID of 200. From this change, I expected only the leaders in department 200 to be shown. However, the leaders for both department 100 and 200 were displayed.

From the original test data, this is how I would explain the logic behind my expected output:

  • 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 (not to be confused with their maximum start 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
6

the join that was getting the max days was by Worker_id. By removing that, we can get the max days worked in total and not by worker. Try the below

select w.* , ldr.*
  from #Worker_Department w
  join (
		SELECT max(DateDiff(day, (start_date), GetDate())) as NowSubMin
		 FROM #Worker_Department
		WHERE position <> 'Leader'
		and Leave_Date is null) maxdate
	on DateDiff(day, (w.start_date), GetDate()) = maxdate.NowSubMin
	and w.Leave_Date is null
  join #Worker_Department ldr
	on w.department_id = ldr.Department_ID
	and ldr.Position = 'Leader'
7

This returned details about worker 10, the manager at department 100. This solves the issue in the original sample data. However, if I change the start date of worker 50 to match that of worker 30's original start date, the expected outcome changes to details about worker 10 and 20. This query will only show the results for the manager at department 100.

I have been able to make a query that provides my expected output.

SELECT DISTINCT mgr.worker_id, 
       mgr.department_id, 
       mgr.position,
       ls.worker_id
FROM   worker_department mgr 
       INNER JOIN(SELECT department_id, 
                         worker_id 
                  FROM   worker_department 
                  WHERE  leave_date IS NULL 
                  HAVING worker_id IN (SELECT ls.worker_id 
                                       FROM   worker_department ls 
                         INNER JOIN (SELECT cw.worker_id 
                                     FROM   worker_department cw 
                                     WHERE 
                         cw.position != 'Leader' 
                         AND cw.leave_date IS NULL) AS cw 
                                 ON ls.worker_id = cw.worker_id 
                                       GROUP  BY ls.worker_id 
                                       HAVING 
                         Datediff(Now(), Min(ls.start_date)) = ( 
                         SELECT 
                         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 
                                              ) 
                         ORDER  BY 
                         nowsubmin DESC 
                          LIMIT  1))) AS ls 
               ON mgr.department_id = ls.department_id 
WHERE  position = 'Leader' 
       AND leave_date IS NULL

I'm not sure if it is the most efficient way of getting the results, but it does work

8

I'm not sure what your requirements are. If you have 2 workers with the same startdate, then you should return both workers with their respective leaders. You are using Limit 1 to return just 1 of them. If you remove the limit, then you should see both workers. It appears to me that you have a lot of stuff going on with your query that you don't need. The query I supplied covers your initial requirement. If there are more, please explain them and their nuances.