# Get The Lowest Date if The other Column is Null

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 Mar 11 '19 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. Mar 11 '19 at 9:15

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

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 Mar 11 '19 at 9:23
• my assumption is that you're correct, but i'm not sure how that plays with an index. Mar 11 '19 at 9:28

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. Mar 11 '19 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 Mar 11 '19 at 9:21