table employee {
    id,
    name
}

table payment_record {
   id,
   type, // 1 is salary, 2-4 is bonus 
   employee_id,
   date_paid,
   amount
}

i want to query employee's newest salary and sum(bonus) from some date. like my payments is like

id, type, employee_id, date_paid, amount
1      1            1  2022-10-01   5000
2      2            1  2022-10-01   1000
3      3            1  2022-10-01   1000     
4      1            1  2022-11-01   3000
5      1            2  2022-10-01   1000
6      1            2  2022-11-01   2000
7      2            2  2022-11-01   3000

query date in ['2022-10-01', '2022-11-01'] show me

employee_id,  employee_name,  newest_salary, sum(bonus)
1             Jeff            3000           2000
2             Alex            2500           3000

which jeff's newest_salary is 3000 becuase there is 2 type = 1(salary) record 5000 and 3000, the newest one is 3000.

and jeff's bonus sum is 1000(type 2) 1000(type 3) = 2000

the current sql i try is like

select
  e.employee_id,
  employee.name,
  e.newest_salary,
  e.bonus 
from 
(
  select 
    payment_record.employee_id, 
    SUM(case when type in ('2', '3', '4') then amount end) as bonus, 
    Max(case when type = '1' then amount end) as newest_salary 
  from 
    payment_record 
  where 
    date_paid in ('2022-10-01',  '2022-11-01')
  group by 
    employee_id
) as e
join 
  employee 
on 
  employee.id = e.employee_id
order by 
  employee_id

it's almost done, but the rule of newest_salary is not correct, i just get the max value althought usually the max value is newest record.

CodePudding user response：

The query is below:

SELECT
  t1.id employee_id,
  t1.name employee_name,
  t3.amount newest_salary,
  t2.bonus bonus
FROM employee t1
  LEFT JOIN
  (
    SELECT
      employee_id,
      MAX(CASE WHEN type=1 THEN date_paid END) date_paid,
      SUM(CASE WHEN type IN (2,3,4) THEN amount END) bonus
    FROM  payment_record
    WHERE date_paid BETWEEN '2022-10-01' AND '2022-11-01'
    GROUP BY employee_id
  ) t2
  ON t1.id=t2.employee_id
  LEFT JOIN payment_record t3
  ON t3.type=1 AND
     t2.employee_id=t3.employee_id AND
     t2.date_paid=t3.date_paid
ORDER BY t1.id

db fiddle

CodePudding user response：

I think Postgres is close enough to work with this solution I tested in sql-server, but it should at least be close enough to translate

My approach is to split the payments in the desired range into salary vs bonus, and sum the bonus but use a partitioned row number to identify the newest salary payment for each employee in the desired date range and only join that one to the bonus totals. Note that I used a LEFT JOIN because an employee might not get a bonus.

DECLARE @StartDate DATE = '2022-10-01';
DECLARE @EndDate   DATE = '2022-11-01';

with cteSample as ( --BEGIN sample data
    SELECT * FROM ( VALUES 
        (1, 1, 1, CONVERT(DATE,'2022-10-01'), 5000)
        , (2, 2, 1, '2022-10-01', 1000)
        , (3, 3, 1, '2022-10-01', 1000)
        , (4, 1, 1, '2022-11-01', 3000)
        , (5, 1, 2, '2022-10-01', 1000)
        , (6, 1, 2, '2022-11-01', 2000)
        , (7, 2, 2, '2022-11-01', 3000)
    ) as TabA(Pay_ID, Pay_Type, employee_id, date_paid, amount) 
) --END Sample data
, ctePayments as ( --Filter down to just the payments in the date range you are interested in
    SELECT Pay_ID, Pay_Type, employee_id, date_paid, amount
    FROM cteSample --Replace this with your real table of payments
    WHERE date_paid >= @StartDate AND date_paid <= @EndDate  
), cteSalary as ( --Identify salary payments in range and order them newest first
    SELECT employee_id, amount
        , ROW_NUMBER() over (PARTITION BY employee_id ORDER BY date_paid DESC) as Newness
    FROM ctePayments as S
    WHERE S.Pay_Type = 1
), cteBonus as ( --Identify bonus payments in range and sum them
    SELECT employee_id, SUM(amount) as BonusPaid
    FROM ctePayments as S
    WHERE S.Pay_Type in (2,3,4)
    GROUP BY employee_id
)
SELECT S.employee_id, S.amount as SalaryNewest
    , COALESCE(B.BonusPaid, 0) as BonusTotal
FROM cteSalary as S --Join the salary list to the bonusa list
    LEFT OUTER JOIN cteBonus as B ON S.employee_id = B.employee_id 
WHERE S.Newness = 1 --Keep only the newest

Result: