SQL查询从过去三年的31/03获取数据

Question

我已经创建了上面的查询，它给了我的奖金标记到人的sysdate。

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的金额，即从31/3/2021、31/03/2020、31/03/2009改为sysdate。

输出就像-

Person_NUMBER   effective_start_Date      current_Amount            2021_AMOUNT         2020_AMOUNT         2019_AMOUNT

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

Answer 1

可以将最后一行作为条件项移到选择列表中，例如

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值的values列。我怀疑您需要获得聚合的结果，以显示汇总的金额，然后考虑添加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