Skip to content
Advertisement

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.

query tried so far

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

Update

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 ?

Advertisement

Answer

From Oracle 12, you can use MATCH_RECOGNIZE.

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement