How to find a specific date using Oracle SQL for employee in most current department?

Question

I'm trying to determine the minimum effective date of an employee that has a record in their most recent department (see data below). I'm looking to have 8/25/2014 as the result but would like some assistance from you experts out there on how to pull it off. In the same data, this employee was in department 70260 for a while and then transferred for a short period of time to 70210 and then came back to 70260. The effective date of 8/25/2014 is that date the employee came back and I'd like to see if there's any creative ways of programming the SQL to show the minimum date of someone in their most recent department. Thanks in advance!!!

╔═══════════╦════════════╦════════╗
║  EMPLID   ║   EFFDT    ║ DEPTID ║
╠═══════════╬════════════╬════════╣
║ 000123338 ║ 10/25/2015 ║  70260 ║
║ 000123338 ║ 4/2/2015   ║  70260 ║
║ 000123338 ║ 2/24/2015  ║  70260 ║
║ 000123338 ║ 11/1/2014  ║  70260 ║
║ 000123338 ║ 8/25/2014  ║  70260 ║ <--- I need the SQL to show 8/25/2014
║ 000123338 ║ 4/27/2014  ║  70210 ║
║ 000123338 ║ 3/16/2014  ║  70210 ║
║ 000123338 ║ 3/6/2014   ║  70260 ║
║ 000123338 ║ 11/1/2013  ║  70260 ║
║ 000123338 ║ 1/24/2013  ║  70260 ║
║ 000123338 ║ 1/1/2013   ║  70260 ║
║ 000123338 ║ 11/1/2012  ║  70260 ║
║ 000123338 ║ 11/1/2011  ║  70260 ║
║ 000123338 ║ 8/1/2010   ║  70260 ║
║ 000123338 ║ 8/5/2009   ║  70260 ║
║ 000123338 ║ 7/1/2009   ║  70260 ║
║ 000123338 ║ 7/7/2008   ║  70260 ║
║ 000123338 ║ 5/5/2008   ║  70260 ║
║ 000123338 ║ 1/1/2008   ║  70260 ║
║ 000123338 ║ 10/29/2007 ║  70260 ║
╚═══════════╩════════════╩════════╝

Answer 1

You need to find the first group of rows:

SELECT EMPLID, MIN(first_dates)
FROM
 (
    SELECT EMPLID, EFFDT, DEPTID,
       CASE -- check if all previous rows return the same value
          WHEN MIN(DEPTID) OVER (PARTITION BY EMPLID ORDER BY EFFDT DESC ROWS UNBOUNDED PRECEDING)
             = MAX(DEPTID) OVER (PARTITION BY EMPLID ORDER BY EFFDT DESC ROWS UNBOUNDED PRECEDING)
          THEN EFFDT
       END AS first_dates -- all other rows return NULL
    FROM tab
 ) dt
GROUP BY 1;

If you want the full row you need to add another ROW_NUMBER:

SELECT ...
FROM
 (
   SELECT ...,
      ROW_NUMBER() OVER (PARTITION BY EMPLID ORDER BY first_dates) AS rn
   FROM
    (
       SELECT EMPLID, EFFDT, DEPTID,
          CASE
             WHEN MIN(DEPTID) OVER (PARTITION BY EMPLID ORDER BY EFFDT DESC ROWS UNBOUNDED PRECEDING)
                = MAX(DEPTID) OVER (PARTITION BY EMPLID ORDER BY EFFDT DESC ROWS UNBOUNDED PRECEDING)
             THEN EFFDT
          END AS first_dates
       FROM tab
    ) dt
 ) dt
WHERE rn = 1