sql query to get the time period the position was tagged to the employee-CodePudding

The table below gives assignment and position details

ASG_NUMBER	START_dATE	END_DATE	JOB_CODE	GRADE_CODE	POS_CDOE
10	01-JAN-2021	08-JUN-2021	S29	Engineer	Manager
10	09-JUL-2021	31-DEC-2021	S29	Sr. Engineer	Manager
10	01-JAN-2022	31-DEC-4712	S56	principal	Sr.Manager
11	16-FEB-2021	22-NOV-2021	A1	Marketing	Analyst
11	23-NOV-2021	31-DEC-4712	A2	Marketing	Analyst
14	09-Feb-2020	18-JUL-2021	G1	Principal	Manager
14	19-JUL-2021	22-SEP-2021	G2	Sr.Principal	Manager
14	23-sep-2021	31-dec-4712	G3	Sr.Principal	Manager

I want the Time in current_Position & Time in Previous Position values which show the time period in years and months the employee was in the current or previous positions

asg_number	Time in current_Position	Time in Previous Position
10	11 m	6 m
11	1y 4 m	1 y 4 m
14	2y 4m	2y 4m

when there is no position change then both these columns should be the same. Which function to use

CodePudding user response：

From Oracle 12, you can use the MATCH_RECOGNIZE and MONTHS_BETWEEN:

SELECT asg_number,
       CASE
       WHEN time_in_post >= 12
       THEN TO_CHAR(TRUNC(time_in_post/12), 'fm90') || 'y '
       END
       ||
       CASE
       WHEN TRUNC(MOD(time_in_post,12)) > 0
       THEN TO_CHAR(TRUNC(MOD(time_in_post,12)), 'fm90') || 'm'
       END AS time_in_post,
       CASE
       WHEN prev_time_in_post >= 12
       THEN TO_CHAR(TRUNC(prev_time_in_post/12), 'fm90') || 'y '
       END
       ||
       CASE
       WHEN TRUNC(MOD(prev_time_in_post,12)) > 0
       THEN TO_CHAR(TRUNC(MOD(prev_time_in_post,12)), 'fm90') || 'm'
       END AS prev_time_in_post
FROM   (
  SELECT asg_number,
         MONTHS_BETWEEN(LEAST(SYSDATE, curr_end_date), curr_start_date)
           AS time_in_post,
         CASE NUM_SAME_POS
         WHEN 1
         THEN MONTHS_BETWEEN(prev_end_date, prev_start_date)
         ELSE MONTHS_BETWEEN(LEAST(SYSDATE, curr_end_date), curr_start_date)
         END AS prev_time_in_post
  FROM   table_name
  MATCH_RECOGNIZE(
    PARTITION BY asg_number
    ORDER     BY end_date DESC
    MEASURES
      FIRST(same_pos_code.end_date)  AS curr_end_date,
      LAST(same_pos_code.start_date) AS curr_start_date,
      COUNT(same_pos_code.end_date)  AS num_same_pos,
      FIRST(diff_pos_code.end_date)  AS prev_end_date,
      LAST(diff_pos_code.start_date) AS prev_start_date
    PATTERN ( ^ same_pos_code  diff_pos_code* )
    DEFINE
      same_pos_code AS FIRST(same_pos_code.pos_code) = pos_code,
      diff_pos_code AS FIRST(diff_pos_code.pos_code) = pos_code
  )
);

Which, for the sample data:

CREATE TABLE table_name (ASG_NUMBER, START_dATE, END_DATE, JOB_CODE, GRADE_CODE, POS_CODE) AS
SELECT 10, DATE '2021-01-01', DATE '2021-06-08', 'S29', 'Engineer',     'Manager' FROM DUAL UNION ALL
SELECT 10, DATE '2021-07-09', DATE '2021-12-31', 'S29', 'Sr. Engineer', 'Manager' FROM DUAL UNION ALL
SELECT 10, DATE '2022-01-01', DATE '4712-12-31', 'S56', 'principal',    'Sr.Manager' FROM DUAL UNION ALL
SELECT 11, DATE '2021-02-16', DATE '2021-11-22', 'A1',  'Marketing',    'Analyst' FROM DUAL UNION ALL
SELECT 11, DATE '2021-11-23', DATE '4712-12-31', 'A2',  'Marketing',    'Analyst' FROM DUAL UNION ALL
SELECT 14, DATE '2020-02-09', DATE '2021-07-18', 'G1',  'Principal',    'Manager' FROM DUAL UNION ALL
SELECT 14, DATE '2021-07-19', DATE '2021-09-22', 'G2',  'Sr.Principal', 'Manager' FROM DUAL UNION ALL
SELECT 14, DATE '2021-09-23', DATE '4712-12-31', 'G3',  'Sr.Principal', 'Manager' FROM DUAL;

Outputs:

ASG_NUMBER TIME_IN_POST PREV_TIME_IN_POST

10 6m 11m

11 1y 4m 1y 4m

14 2y 4m 2y 4m

ASG_NUMBER	TIME_IN_POST	PREV_TIME_IN_POST
10	6m	11m
11	1y 4m	1y 4m
14	2y 4m	2y 4m

db<>fiddle here