Want to display employee's id, sum of last 3 months salary, sum of last 6 month salary, sum of 1 year salary in SQL select query

Question

I want to display employee name, ID, sum of last 1 month salary, sum of last 3 months salary, sum of last 6 months salary, sum of last 1 year salary in a select statement. Employee get salary on daily basis so sum(SALARY) function to get total sum. TABLES: Employee, department and Salary(contains SALARY and DATE column )

with t1 as (
select * from employee A
join department B ON B.emp_id=A.emp_id
join salary C on C.dep_id=B.dep_id
)

select first_name, last_name, ID,
'' as 1_month_salary,
'' as 3_month_salary,
'' as 6_month_salary,
'' as 1_year_salary
from t1;

I need dates logic for adding values based on dates and group by the columns.

Answer 1

You'll likely want to aggregate conditionally with a case expression:

SELECT first_name, last_name, ID, 
    SUM(CASE WHEN whatever_date_column BETWEEN add_months(sysdate,-1) AND sysdate THEN salary_column) END as 1_month_salary, 
    SUM(CASE WHEN whatever_date_column BETWEEN add_months(sysdate,-3) AND sysdate THEN salary_column) END as 3_month_salary,
    SUM(CASE WHEN whatever_date_column BETWEEN add_months(sysdate,-6) AND sysdate THEN salary_column) END as 6_month_salary
FROM t1
GROUP BY first_name, last_name, ID