sql query to get previous and current job and position data

Question

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 ?

MT0 · Accepted Answer · 2022-07-04T08:01:18.027

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

The prev_time_in_post is giving an error, what is the '90"m"'? — SSA_Tech124, Jul 04 '22 at 04:41
hey @MT0, The above query is working fine. But is there a way i pull the current effective data and previous effective date only for the dates when there was change in grade & job? i.e. if there is an update in position but not in job, grade for the employee then current effective date should be same as previosu effective date. and if there is change in job, grade then current effective date should be the date when that grade or job change was done — SSA_Tech124, Jul 06 '22 at 04:55

sql query to get previous and current job and position data

1 Answers1