SQL query to get the data as of 31/03 of past three years-CodePudding

I have created the above query which give me the Bonus tagged to the person as of 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

I want to tweak the above query to get the Amount for past three years as of 31/3 i.e. instead of sysdate as of 31/3/2021, 31/03/2020,31/03/2019

Output like -

Person_NUMBER   effective_start_Date      current_Amount            2021_AMOUNT         2020_AMOUNT         2019_AMOUNT

How can i tweak the same query and change the sysdate condition to look for data for past three years as well for the 2021_amount, 2020_amount and 2019_amount column

CodePudding user response：

You can move the last line to the SELECT list as conditionals such as

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'

in order to get the currently displayed result, but in this case there will be multiple lines with NULL values for amount columns. I suspect that you need to get aggregated results in order to show the summed up amounts, then consider adding SUM() aggregation such as

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

CodePudding user response：

You can insert multiple select in the same query and name the result as you want. Here is an example of what I meant

     SELECT a.distributor_id,
(SELECT COUNT(*) FROM myTable WHERE level='personal' and distributor_id = a.distributor_id) as PersonalCount,
(SELECT COUNT(*) FROM myTable WHERE level='exec' and distributor_id = a.distributor_id) as ExecCount,
(SELECT COUNT(*) FROM myTable WHERE distributor_id = a.distributor_id) as TotalCount

FROM (SELECT DISTINCT distributor_id FROM myTable) a ;