2

I have the following

CREATE TABLE employees (employee_id int, from_date date, to_date date);

INSERT INTO employees(Employee_ID,From_Date,To_Date)
VALUES
    (1    ,       '1998-01-02'   ,  NULL),
    (1    ,       '2000-01-01'   ,  NULL),
    (1    ,       '2015-01-01'   ,  '2018-01-01'),
    (2    ,       '2005-01-01'   ,  '2006-01-01'),
    (2    ,       '2007-01-01'   ,  '2007-05-05'),
    (3    ,       '2002-02-02'   ,  '2004-02-02'),
    (3    ,       '2010-01-01'   ,  '2015-01-01'),
    (4    ,       '2001-01-01'   ,  '2005-03-03'),
    (4    ,       '2003-03-03'   ,  '2004-04-04');

I want to select all the records with the lowest From_Date only if the record has a To_Date and the time duration between the From_Date and To_Date of that record is less than three years. The result should look like below:

Employee_ID     From_Date         To_Date
    2          2005-01-01       2006-01-01
    3          2002-02-02       2004-02-02

In other words if any employee is missing the To_Date in their lowest From_Date record and if the record with lowest From_Date has a To_Date but the time_duration is more than 3 years, they shouldn't be considered. As it can be seen in above result table the employee with ID 1 is not considered because the lowest From_Date record has not To_Date and the Employee with ID 4 is not considered because the time duration between From_Date and To_Date of the lowest record is more than three years.

  • what do you do if you have two from dates that are equal both with a to_date < 3 years – Evan Carroll Mar 11 at 9:05
  • It should pick one of them, That isn't the problem if it picks the first ever record by lowest from date, if there is another record with same from date it should return unique ones. – Sayad Xiarkakh Mar 11 at 9:15
1

It's a two step problem: find the rows with the lowest from_date for each employee (aka "greatest-n-per-group" problem) and then filter out those where the difference is less than three years:

with emps as (
  select distinct on (employee_id) employee_id, from_date, to_date
  from employees
  where to_date is not null
  order by employee_id, from_date 
) 
select *
from emps
where to_date::timestamp - from_date::timestamp < interval '3 year';

Online exmaple: https://rextester.com/QTLYEZ74319

1

Here is what I came up with

SELECT employee_id, from_date, to_date
FROM employees AS e1
WHERE to_date IS NOT NULL -- may not be useful as per comments.
  AND to_date - from_date <= (365 * 3)
  AND EXISTS (
    SELECT
    FROM employees AS e2
    WHERE e1.employee_id = e2.employee_id
    GROUP BY employee_id
    HAVING e1.from_date = min(e2.from_date)
  );
  • I think to_date is not null is not really needed as to_date - from_date <= (365 * 3) will be null (and thus "false") anyway if to_date is null – a_horse_with_no_name Mar 11 at 9:23
  • my assumption is that you're correct, but i'm not sure how that plays with an index. – Evan Carroll Mar 11 at 9:28
0

You could do this with row_number()

WITH CTE
AS
(
SELECT Employee_ID,from_Date,To_Date,ROW_NUMBER() OVER(PARTITION BY Employee_ID order by from_Date asc,To_Date asc ) as rownum
FROM Employees

)
SELECT Employee_ID,from_Date,To_Date
FROM CTE C
WHERE DATE_PART('year', To_Date::date) - DATE_PART('year',From_Date::date) < 3
AND  to_date IS NOT NULL
AND rownum = 1
ORDER BY C.Employee_ID;

Or a Self join

WITH CTE
AS
( 
SELECT Employee_ID,min(from_date) as from_date
FROM Employees 
GROUP BY Employee_ID
)

SELECT  C.Employee_ID,C.from_Date,MIN(E.To_Date)
FROM CTE C
INNER JOIN Employees E
on E.Employee_ID = C.Employee_ID AND C.from_Date = E.from_Date
WHERE DATE_PART('year', E.To_Date::date) - DATE_PART('year',C.From_Date::date) < 3

AND to_date IS NOT NULL GROUP BY C.Employee_ID,C.from_Date ORDER BY C.Employee_ID;

Result

employee_id from_date   to_date
2   2005-01-01  2006-01-01
3   2002-02-02  2004-02-02

DB<>Fiddle

  • It will not consider the null values, it gives you the record of employee with ID 1 as well which has the to_date filled. – Sayad Xiarkakh Mar 11 at 9:07
  • @Sayad fixed it, NULLs will be taken out after the grouping / row_numbering as to only filter on one record for each employeeID – Randi Vertongen Mar 11 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.