1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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

Discussion in 'MySQL' started by Curtis_L, Feb 6, 2020.

  1. #1
    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):
     
    Last edited by a moderator: Feb 7, 2020
    Curtis_L, Feb 6, 2020 IP
  2. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #2
    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...
     
    Last edited: Feb 6, 2020
    JEET, Feb 6, 2020 IP
  3. Curtis_L

    Curtis_L Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    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.
     
    Curtis_L, Feb 7, 2020 IP
  4. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #4
    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
     
    JEET, Feb 8, 2020 IP