How to Use MATCH_RECOGNIZE and MONTHS_BETWEEN to Calculate Time Spent in Each Position

Introduction to SQL and Time Period Calculation

As a technical blogger, it’s essential to explore the world of SQL and time period calculations. In this article, we’ll dive into a specific problem where you want to calculate the time period an employee was in their current or previous position.

Problem Statement

The table below provides 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

The goal is to calculate the time period an employee was in their current or previous position. The table with desired output:

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

Using MATCH_RECOGNIZE and MONTHS_BETWEEN

From Oracle 12, you can use the MATCH_RECOGNIZE and MONTHS_BETWEEN clauses to achieve this.

The Formula:

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
  )
);

Explanation:

The MATCH_RECOGNIZE clause is used to identify the start and end dates of each position.

The PARTITION BY clause groups the rows by the ASG_NUMBER.
The ORDER BY clause sorts the rows in descending order based on the END_DATE.
The MEASURES clause specifies the columns that are used to identify the start and end dates of each position.
- FIRST(same_pos_code.end_date) returns the earliest END_DATE for the current position.
- LAST(same_pos_code.start_date) returns the latest START_DATE for the current position.
- COUNT(same_pos_code.end_date) counts the number of rows with the same position code.
The PATTERN ( ^ same_pos_code+ diff_pos_code* ) clause specifies that there should be at least one row with a new position code (same_pos_code) followed by zero or more rows with different position codes (diff_pos_code*).
The DEFINE clause defines two variables: same_pos_code and diff_pos_code.
- same_pos_code returns the first value of pos_code that matches any row.
- diff_pos_code returns the first value of pos_code that is different from the previous one.

Creating the Sample Data

To test the query, we need to create a sample table with the required 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;

Output

The query produces the desired output:

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

Conclusion

In this article, we explored how to use MATCH_RECOGNIZE and MONTHS_BETWEEN in Oracle SQL to calculate the time period an employee was in their current or previous position. We created a sample table with required data, explained the query, and provided the output. This technique can be applied in various scenarios where you need to analyze employee positions and calculate the time spent in each position.

Last modified on 2025-04-11