clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2249077 fiddles created (32134 in the last week).

CREATE TABLE Employees(Employee_ID int, From_Date date, To_Date date);
 hidden batch(es)


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 , '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')
10 rows affected
 hidden batch(es)


SELECT Employee_ID,min(from_date) as from_date FROM Employees WHERE to_date IS NOT NULL GROUP BY Employee_ID
employee_id from_date
3 2002-02-02
4 2001-01-01
2 2005-01-01
1 2015-01-01
 hidden batch(es)


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 WHERE to_date IS NOT NULL
employee_id from_date to_date rownum
1 2015-01-01 2018-01-01 1
2 2005-01-01 2006-01-01 1
2 2005-01-01 2006-01-01 2
2 2007-01-01 2007-05-05 3
3 2002-02-02 2004-02-02 1
3 2010-01-01 2015-01-01 2
4 2001-01-01 2005-03-03 1
4 2003-03-03 2004-04-04 2
 hidden batch(es)


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;
employee_id from_date min
2 2005-01-01 2006-01-01
3 2002-02-02 2004-02-02
 hidden batch(es)


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;
employee_id from_date to_date
2 2005-01-01 2006-01-01
3 2002-02-02 2004-02-02
 hidden batch(es)