Find employees that were earning above 120k, but now do not

Hi guys,

I have this code:

WITH

CTE_Population AS
(
    select e.PERSON_REF
    from D550M e
    where e.START_DATE <= TRUNC(SYSDATE) and coalesce(e.END_DATE, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
),

CTE_EmployeeInfo AS
(
    select e.PERSON_REF,
           e.EMPLOYEE_NUMBER EMPNO,
           per.SURNAME || ', ' || per.FIRST_FORNAME NAME,
           per.SURNAME       SURNAME,
           per.FIRST_FORNAME FORENAME,
           per.NI_NO         NINO,
           per.INITIALS      INITS,
           per.TITLE         TITLE,
           per.KNOWN_AS      KNOWNAS,
           e.START_DATE      EMPSTARTDATE,
           e.END_DATE        EMPENDDATE,
           CGrp.ID           CGRPID,
           CGrp.LONG_DESC    CGRPDESC,
           PGrp.ID           PGRPID,
           PGrp.LONG_DESC    PGRPDESC

    from      D550M e
    left join D500M per   on e.PERSON_REF = per.PERSON_REF
    left join D100M CGrp  on e.LEVEL1_PAY_STR_REF = CGrp.REF
    left join D100M PGrp  on e.PAY_STR_REF = PGrp.REF  
),

CTE_Salary AS
(
    select fixPE.PERSON_REF,
           fixPE.AMOUNT * 12 as SALARY,
           fixPE.id
           

    from      D555M fixPE
    left join D550M e     on fixPE.PERSON_REF = e.PERSON_REF
)

select e.EMPNO,
       e.SURNAME,
       e.FORENAME,
       SALARY,
       id,
       EMPSTARTDATE
       

from      CTE_Population   pop
left join CTE_EmployeeInfo e   on pop.PERSON_REF = e.PERSON_REF
left join CTE_Salary sal   on pop.PERSON_REF = sal.PERSON_REF

where salary is not null
and salary > '120000'
and EMPSTARTDATE = sysdate
and id >= '1000' 

Select allOpen in new window

I need to find all employees which used to earn 1200k or above, but now ever earn less than 120k.

Thanks.

EDIT: I think this table could hold the current wage, not the historic ones...