I have two tables
PAPF
PERSON_ID NAME
19 XYZ
8 ABC
10 YGDF
1 SMITH
PEEVF
EFFECTIVE_START_DATE EFFECTIVE_END_DATE PERSON_ID ELEMENT_NAME VALUE
01-JAN-2021 10-JAN-2021 19 Bonus 9.1
11-JAN-2021 28-MAR-2021 19 Bonus 11
29-MAR-2021 10-APR-2021 19 Bonus 11.1
11-APR-2021 31-DEC-2021 19 Bonus 10
11-APR-2022 30-APR-2022 19 Bonus 12
19-APR-2022 30-MAY-2022 19 LTP 09
01-JAN-2019 30-APR-2019 8 Bonus 5
11-MAR-2021 10-JAN-2021 10 Bonus 11
11-JAN-2021 28-MAR-2021 10 Bonus 11.1
20-JAN-2019 08-MAR-2019 1 LTP 9
11-APR-2019 19-AUG-2019 1 LTP 9.8
I want to create a query such that I get the latest Bonus and LTP for an employee in each particular year.
The current query is as follows -
select name, element_name, value
from per_all_people_f papf ,
per_element_f PEEVF
where peevf.person_id = papf.person_id
and sysdate between papf.effective_start_Date and papf.effective_end_Date
the output should look like -
PERSON_ID CURRENT_YEAR_BONUS CURRENT_YEAR_LTP FY2019_LTP FY2021_LTP FY2020_LTP FY2021_BONUS FY2020_BONUS FY2019_BONUS
19 12 09 10
8 5
10 11.1
1 9.8