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.