Search code examples
sqloraclebi-publisher

How to get the tenure in current job role?

Given the current job code (e.g., DA123-2) and the previous job code (e.g., DA123-1), calculate the tenure starting from the date of the previous job code (ignoring anything after the hyphen in the job code).

In the example provided, the employee’s job role changed four times: ITW124 -> DA123 -> DA123-1 -> DA123-2. Since the last three job codes are the same before the hyphen, we should consider the last job role change as of March 23, 2024.

Please see the sample data tables below

We cannot use the Analytical functions(LAG/LEAD) as it is not supported. I tried with Previous dates on the Job_ID but no luck.

sample data

Solution

  • From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row pattern matching and find the earliest date from the latest roles where the stem of the job-code is unchanged:

    WITH emp_jobs (eid, changedate, job_code) AS (
  SELECT e.eid,
         e.changedate,
         REGEXP_REPLACE(j.job_code, '-.*$')
  FROM   employee e
         INNER JOIN job j
         ON e.job_id = j.job_id
)
SELECT eid,
       TRUNC(SYSDATE) - changedate AS tenure
FROM   emp_jobs
       MATCH_RECOGNIZE(
         PARTITION BY eid
         ORDER BY changedate DESC
         MEASURES
           LAST(changedate) AS changedate
         PATTERN ( ^ same_job+ )
         DEFINE same_job AS FIRST(job_code) = job_code
       )

    If you cannot use MATCH_RECOGNIZE or analytic functions then you can use:

    WITH emp_jobs (eid, changedate, job_code) AS (
  SELECT e.eid,
         e.changedate,
         REGEXP_REPLACE(j.job_code, '-.*$')
  FROM   employee e
         INNER JOIN job j
         ON e.job_id = j.job_id
)
SELECT eid,
       TRUNC(SYSDATE) - MIN(changedate) AS tenure
FROM   emp_jobs ej
WHERE  NOT EXISTS (
         SELECT 1
         FROM   emp_jobs x
         WHERE  ej.eid = x.eid
         AND    ej.job_code != x.job_code
         AND    ej.changedate < x.changedate
       )
GROUP BY eid

    Which, for the sample data:

    CREATE TABLE employee (eid, job_id, changedate) AS
SELECT 123, 1001, TRUNC(SYSDATE) -   0 FROM DUAL UNION ALL
SELECT 123, 2002, TRUNC(SYSDATE) -  37 FROM DUAL UNION ALL
SELECT 123, 3003, TRUNC(SYSDATE) - 110 FROM DUAL UNION ALL
SELECT 123, 4004, TRUNC(SYSDATE) - 150 FROM DUAL UNION ALL
SELECT 123, 3003, TRUNC(SYSDATE) - 300 FROM DUAL;

CREATE TABLE job (job_id, job_code) AS
SELECT 1001, 'DA123-2' FROM DUAL UNION ALL
SELECT 2002, 'DA123-1' FROM DUAL UNION ALL
SELECT 3003, 'DA123'   FROM DUAL UNION ALL
SELECT 4004, 'ITW124'  FROM DUAL;

    Both output:

    EID TENURE
    123 110

    fiddle