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' 

Open 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...
Richiep86Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I need to find all employees which used to earn 1200k or above, but now ever earn less than 120k.
I must admit that the sql you show is a bit confusing, respectively not clear to say what to do from there:

I will hence instead just tell you want to do "in general" for such a query.
consider these 2 tables:
employees ( id, name )  -> 1 row per employe
employee_salary ( emp_id, month_date, salary )  -> 1 row per month per employee, with for example the first day of the month as reference.

the sql would go like this:
select e.*
  from employee e
  where exists( select null from employee_salary s where s.emp_id = e.id and s.salary < 120000 and s.month_date >= trunc(sysdate, 'MM'))
    and exists( select null from employee_salary s where s.emp_id = e.id and s.salary >= 120000 and s.month_date < trunc(sysdate, 'MM'))

Open in new window

hope this helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richiep86Author Commented:
this does help - many thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.