SQL查询以获取先前和当前的工作和位置数据

Question

我有一个桌子ASG，如下所示 -

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
        

我想确定成绩和工作变化的员工，然后显示当前和以前的工作和职位数据。 这可以像下面的那样，

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     

如何使用滞后函数来获得当前和先前的作业，等级和位置的这些值？

Answer 1

在lag（...）上使用（...按END_DATE ASC订购）或lead> lead（...）通过（...按END_DATE DESC订购）>分析功能，然后找到每个分区的最后一行，请使用row_number分析功能：

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＆lt;＆gt; fiddle “ nofollow noreferrer”>在这里