sql query to get previous and current job and position data-CodePudding

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 ?

CodePudding user response：

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