I have a table asg is like below -
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
I want to identify employees who have had a change in Grade and Job and then show the current and previous job and position data. This can be like below
ASG_NUMBER CUR_POS_CODE CUR_JOB_CODE CUR_GRADE_CODE PREV_JOB_CODE PREV_GRADE_CODE PREV_POS_CODE Curr_date Prev_date Time in previous pos(Y m)
10 Sr.Manager S56 PRINCIPAL S29 Sr.engineer Manager 1-jan-2022 09-JUL-2021 2 y 0 m
11 Analyst A2 Marketing A1 23-Nov-2022 16-fen-2021 9m
How can i use lag function to get these values for current and previous job , grade and position ?
Use either the LAG(...) OVER (... ORDER BY end_date ASC) or LEAD(...) OVER (... ORDER BY end_date DESC) analytic functions and then, to find the last row of each partition, use the ROW_NUMBER analytic function:
SELECT asg_number,
curr_pos_code,
curr_job_code,
curr_grade_code,
prev_pos_code,
prev_job_code,
prev_grade_code,
curr_date,
prev_date,
CASE
WHEN prev_time_in_post >= 12
THEN TO_CHAR(TRUNC(prev_time_in_post/12), '90') || 'y '
END
||
CASE
WHEN TRUNC(MOD(prev_time_in_post,12)) > 0
THEN TO_CHAR(TRUNC(MOD(prev_time_in_post,12)), '90') || 'm'
END AS prev_time_in_post
FROM (
SELECT asg_number,
pos_code AS curr_pos_code,
job_code AS curr_job_code,
grade_code AS curr_grade_code,
LEAD(pos_code) OVER (PARTITION BY asg_number ORDER BY end_date DESC)
AS prev_pos_code,
LEAD(job_code) OVER (PARTITION BY asg_number ORDER BY end_date DESC)
AS prev_job_code,
LEAD(grade_code) OVER (PARTITION BY asg_number ORDER BY end_date DESC)
AS prev_grade_code,
start_date AS curr_date,
LEAD(start_date) OVER (PARTITION BY asg_number ORDER BY end_date DESC)
AS prev_date,
LEAD(MONTHS_BETWEEN(end_date, start_date))
OVER (PARTITION BY asg_number ORDER BY end_date DESC)
AS prev_time_in_post,
ROW_NUMBER() OVER (PARTITION BY asg_number ORDER BY end_date DESC)
AS rn
FROM table_name
)
WHERE rn = 1;
db<>fiddle here