how to get start date of latest dept in which working

I’m trying to get the record of emp current working dept with start date

Data, in below data Grp column is I’ve derived to get the 3rd last record, which is the correct result.

loginName   emp_name    dept_id     dept_name                   startDate   endDate     Grp
KK          KKLeo       5652        POLO Business – Tech        18-09-17    19-09-17    1
KK          KKLeo       5652        POLO Business – Tech        19-09-17    28-09-17    1
KK          KKLeo       5652        POLO Business – Tech        28-09-17    09-11-17    1
KK          KKLeo       5647        POLO Business               09-11-17    15-03-19    2
KK          KKLeo       5647        POLO Business               15-03-19    16-04-19    2
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   16-04-19    03-05-19    3
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   03-05-19    11-10-19    3
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   11-10-19    22-07-20    3
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   22-07-20    26-08-20    3
KK          KKLeo       5652        POLO Business – Tech        26-08-20    17-02-21    4
KK          KKLeo       5652        POLO Business – Tech        17-02-21    19-02-21    4
KK          KKLeo       5652        POLO Business – Tech        19-02-21    null        4

query tried so far

select login_name,
       emp_name,
       dept_id,
       dept_name,
       startDate,
       endDate, 
       sum (counter) OVER (PARTITION BY emp_name order by startDate) grp
                            from (
                                    select login_name,
                                           emp_name,
                                           dept_id,
                                           dept_name,
                                           startDate,
                                           endDate,
                                           CASE WHEN DEPT_NAME = LAG(DEPT_NAME) OVER (PARTITION BY login_name, emp_name, dept_id ORDER BY startDate)
                                                THEN 0
                                                ELSE 1
                                           END counter
                                    FROM empdepthist 
                               )

Am not able to think ahead, that how can I use Grp now to get 3rd last record as a output. Data is having only one emp details but it could be many.

expected output

KK          KKLeo       5652        POLO Business – Tech        26-08-20    17-02-21

Update

oginName   emp_name    dept_id     dept_name                   startDate   endDate     PartitionByEMpName   GroupInPartiton    
KK          KKLeo       5652        POLO Business – Tech        18-09-17    19-09-17    1                   a
KK          KKLeo       5652        POLO Business – Tech        19-09-17    28-09-17    1                   a
KK          KKLeo       5652        POLO Business – Tech        28-09-17    09-11-17    1                   a
KK          KKLeo       5647        POLO Business               09-11-17    15-03-19    1                   b
KK          KKLeo       5647        POLO Business               15-03-19    16-04-19    1                   b
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   16-04-19    03-05-19    1                   c
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   03-05-19    11-10-19    1                   c
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   11-10-19    22-07-20    1                   c
KK          KKLeo       5649        POLO Buss-Fixed Sales Mkt   22-07-20    26-08-20    1                   c   
KK          KKLeo       5652        POLO Business – Tech        26-08-20    17-02-21    1                   d   
KK          KKLeo       5652        POLO Business – Tech        17-02-21    19-02-21    1                   d
KK          KKLeo       5652        POLO Business – Tech        19-02-21    null        1                   d

Is it correct to visualize in this way ? PartitionByEMpName coulmn value is derived from Partition by loginName and GroupInPartiton is derived from DEFINE same_dept AS FIRST(dept_id) = dept_id and once its in this format, then Pattern and measures will work on this data. Is correct ?

Answer

From Oracle 12, you can use MATCH_RECOGNIZE.

SELECT *
FROM   empdepthist
MATCH_RECOGNIZE (
  PARTITION BY loginname
  ORDER BY startdate
  MEASURES
    FIRST(emp_name) AS emp_name,
    FIRST(dept_id) AS dept_id,
    FIRST(dept_name) AS dept_name,
    FIRST(startDate) AS startdate,
    FIRST(endDate) AS enddate
  ONE ROW PER MATCH
  PATTERN (same_dept+ $)
  DEFINE same_dept AS FIRST(dept_id) = dept_id
)

It works by:

1. PARTITION the rows BY loginname.
2. Within each partition, ORDER the rows BY startdate.
3. DEFINE the same_dept pattern as sequential rows (within the partition in the given order) where the FIRST(dept_id) in the match equals the dept_id of each other row in the match.
4. The PATTERN to be matched is at the end of the ordering ($) which matches the pattern defined as one-or-more rows (+) matching the same_dept pattern.
5. When you have matched the pattern, return only ONE ROW PER MATCH.
6. That row will contain the columns in the PARTITION BY clause and the values defined in the MEASURES clause which, in this case, is the value from the FIRST row of the match.

Which, for the sample data, outputs:

LOGINNAME	EMP_NAME	DEPT_ID	DEPT_NAME	STARTDATE	ENDDATE
KK	KKLeo	5652	POLO Business – Tech	26-08-20	17-02-21

db<>fiddle here