CODEWITHSUNDEEP
sqloracle-database
SSA_Tech124
SSA_Tech124

Reputation: 599

SQL query to return the latest date when the column has changed

I have a table per_all_assignments_m paam that has position_id and other object ids. This table has effetive_start_Date and effective_end_date. Whenever there is an update in this table, the effective_start_Date changes to the date when the change was made.

assignment_number    person_id             eff_start_Date         eff_end_date         POSISTION_ID    GRADE_ID        
E12                   12                     12-JAN-2020           31-DEC-4712           10                 2         
E12                   12                     25-NOV-2019           11-JAN-2020           10                 2         
E12                   12                     02-APR-2019           24-NOV-2019           8                  2
---
E11                   11                    12-JAN-2020           31-DEC-4712           10                 2         
E11                   11                     25-NOV-2019           11-JAN-2020           10                 2         
E11                   11                     02-APR-2019           24-NOV-2019           10                  2

The below query will not return anything for PREv_Job_TItle since there is no change in position_id in the latest 2 rows.

I want to fetch another column in the below query - cur_eff_dt. If there is no change in the latest rows as of trunc(sysdate) in the PAAM table, then the cur_eff_dt should return the last date when the position_id was changed or the first date that position was tagged to the employee. From the above table - cur_eff_dt should be 25-Nov-2019 for employee E12. FOR Employee number - E11 it should be 02-APR-2019.

Any function through which this can be achieved ?

    select distinct 
PAPF.PERSON_NUMBER, 
    NULLIF((select distinct name
from   (
  select distinct m.person_id,m.position_id,h.name,
         lead(m.position_id,1,0) over (order by m.position_id) as nextr ,
         lag(m.position_id,1,0) over (order by m.position_id) as prevr        
  from   per_all_assignments_M m,HR_ALL_POSITIONS_F F,hr_all_positions_f_vL h
  where m.position_id=f.position_id
             and f.position_id=h.position_id
 and sysdate between h.effective_start_date and h.effective_end_date
and m.person_id=papf.person_id
and h.effective_end_date=TO_DATE('12/31/4712 00:00:00', 'MM/DD/YYYY HH24:MI:SS'))
where nextr<>prevr and rownum=1),POS.NAME)PREv_Job_TItle,
     POS.NAME Current_job_title


from    per_all_people_f PAPF
        ,PER_PERSON_NAMES_F PPNF
        ,PER_PERIODS_OF_SERVICE PPOS
        ,PER_ALL_ASSIGNMENTS_M PAAM
        ,PER_PERSON_TYPES PPT
        ,PER_GRADES_F_TL PGF
        ,hr_all_positions_f_vL POS
        ,PER_JOBS_F PJF


where   1=1
and papf.person_number = '111'

        AND PPOS.PERSON_ID =PAPF.PERSON_ID
        AND PPOS.PERSON_ID=PPNF.PERSON_ID
        AND PPNF.NAME_TYPE = 'GLOBAL'
        AND PAAM.PERSON_ID=PAPF.PERSON_ID
        AND PPOS.PERSON_ID=PAAM.PERSON_ID
        AND PPOS.LEGISLATION_CODE=PAAM.LEGISLATION_CODE
        AND PAAM.PRIMARY_ASSIGNMENT_FLAG = 'Y'
        AND PAAM.ASSIGNMENT_TYPE = 'E' 
        AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
        AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
        AND PAAM.PRIMARY_FLAG='Y'
        AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
        AND PGF.GRADE_ID(+)=  PAAM.GRADE_ID
        AND PGF.LANGUAGE='US'
        AND POS.POSITION_ID(+)=PAAM.POSITION_ID
        AND PJF.JOB_ID(+)=PAAM.JOB_ID
                        AND PAAM.EFFECTIVE_END_DATE=TO_DATE('12/31/4712 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
        AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

        AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
        AND TRUNC(SYSDATE) BETWEEN PGF.EFFECTIVE_START_DATE AND PGF.EFFECTIVE_END_DATE
        AND TRUNC(SYSDATE) BETWEEN POS.EFFECTIVE_START_DATE AND POS.EFFECTIVE_END_DATE

Upvotes: 0

Views: 684

Answers (1)

Popeye
Popeye

Reputation: 35900

I am not sure about your current query but you can achieve what is required using following query:

Select person_id, assignment_number,
       Max(effective_start_date) keep (dense_rank last order by rn) as cut_off_date
From
(Select t.*, 
        case when lead(position_id) 
          over (partition by person_id, assignment_number order by effective_start_date)
        <> position_id then 1 else 0 end as rn
From per_all_assigments_m t
Where person_id in (11,12))
Group by person_id, assignment_number

Cheers!!

Upvotes: 0

Related Questions