SQL查询以获取截至过去三年的31/03的数据

Question

我已经创建了上面的查询，该查询为我提供了标记为截至sysdate的人员的Bonus。

select 
person_number ,
peef.effective_start_Date,
peef.value Amount
from 
per_all_people_f papf,
pay_element_entries_f peef
where 
papf.person_id = peef.person_id
and PEEF.element_name in ('Bonus')
and sysdate between peef.effective_start_Date and peef.effective_end_Date

我想调整以上查询，以获得截至31/3的过去三年的金额，即，而不是截至2021年3月31日、2020年3月3日、2019年3月1日的系统日期

输出类似

Person_NUMBER   effective_start_Date      current_Amount            2021_AMOUNT         2020_AMOUNT         2019_AMOUNT

我如何调整同一查询并更改sysdate条件以查找过去三年的数据以及2021_amount、2020_aunt和2019_amount列的数据

Answer 1

您可以将最后一行作为条件语句(如(移动到SELECT列表中

SELECT person_number, peef.effective_start_Date, 
peef.value AS current_amount,
CASE WHEN date'2021-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date 
THEN peef.value 
END AS 2021_amount,
CASE WHEN date'2020-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date 
THEN peef.value 
END AS 2020_amount,
CASE WHEN date'2019-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date 
THEN peef.value 
END AS 2019_amount                          
FROM per_all_people_f papf
JOIN pay_element_entries_f peef
ON peef.person_id = papf.person_id
WHERE peef.element_name = 'Bonus'

以便获得当前显示的结果，但在这种情况下，金额列将有多行值为NULL。我怀疑您需要获得聚合结果才能显示合计金额，然后考虑添加SUM((聚合，如

SELECT person_number, peef.effective_start_Date, 
SUM( peef.value ) AS current_amount,
SUM( CASE WHEN date'2021-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date 
THEN peef.value 
END ) AS 2021_amount,
SUM( CASE WHEN date'2020-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date 
THEN peef.value 
END ) AS 2020_amount,
SUM( CASE WHEN date'2019-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date 
THEN peef.value 
END ) AS 2019_amount                          
FROM per_all_people_f papf
JOIN pay_element_entries_f peef
ON peef.person_id = papf.person_id
WHERE peef.element_name = 'Bonus'
GROUP BY person_number, peef.effective_start_Date