Postgresql – Conditionally multiply by relation

postgresql

I have the following postgresql query that takes some time entries and multiplies them by the latest staff rate that occurs before that time entry. Outlined here Multiply by value from related row with latest date

drop table if exists data  cascade;
create table time_table (id int, staff_id integer, entry_date date, hours numeric);
insert into time_table
values
(1, 1, to_date('15-01-2019', 'dd-mm-yyyy'), 1),
(2, 1, to_date('15-02-2019', 'dd-mm-yyyy'), 2),
(3, 1, to_date('15-03-2019', 'dd-mm-yyyy'), 3),
(4, 2, to_date('15-01-2019', 'dd-mm-yyyy'), 4),
(5, 2, to_date('15-02-2019', 'dd-mm-yyyy'), 5),
(6, 2, to_date('15-03-2019', 'dd-mm-yyyy'), 6);

create table rates_table (id int, staff_id integer, start_date date, rate numeric);
insert into rates_table
values
(1, 1, to_date('01-01-2019', 'dd-mm-yyyy'), 1),
(2, 1, to_date('01-02-2019', 'dd-mm-yyyy'), 2),
(3, 1, to_date('01-03-2019', 'dd-mm-yyyy'), 3),
(4, 2, to_date('01-01-2019', 'dd-mm-yyyy'), 4),
(5, 2, to_date('01-02-2019', 'dd-mm-yyyy'), 5),
(6, 2, to_date('01-03-2019', 'dd-mm-yyyy'), 6);

SELECT t.staff_id, t.entry_date, t.hours * r.rate AS total_rate 
FROM   time_table t
LEFT   JOIN LATERAL (
   SELECT r.rate
   FROM   rates_table r
   WHERE  r.staff_id = t.staff_id
   AND    r.start_date <= t.entry_date -- "on or before that time entry"
   ORDER  BY r.start_date DESC NULLS LAST
   LIMIT  1
   ) r ON true;

outputs:

staff_id | entry_date | total_rate
----------------------------------
1        | 15-01-2019 | 1
1        | 15-02-2019 | 4
1        | 15-03-2019 | 9
2        | 15-01-2019 | 16
2        | 15-02-2019 | 25
2        | 15-03-2019 | 36

I'm trying to introduce role rates, which will be used instead of the staff rate if a matching role rate exists.

That introduces a new role_rates table listing the rates for each role and a new staff table linking a staff member to a role.

I've attempted the following but I don't seem to be correctly joining the roles rates to the query.

drop table if exists data  cascade;
create table time_entries (id int, staff_id integer, entry_date date, hours numeric);
insert into time_entries
values
(1, 1, to_date('15-01-2019', 'dd-mm-yyyy'), 1),
(2, 1, to_date('15-02-2019', 'dd-mm-yyyy'), 2),
(3, 1, to_date('15-03-2019', 'dd-mm-yyyy'), 3),
(4, 2, to_date('15-01-2019', 'dd-mm-yyyy'), 4),
(5, 2, to_date('15-02-2019', 'dd-mm-yyyy'), 5),
(6, 2, to_date('15-03-2019', 'dd-mm-yyyy'), 6);

create table staff_rates (id int, staff_id integer, start_date date, rate numeric);
insert into staff_rates
values
(1, 1, to_date('01-01-2019', 'dd-mm-yyyy'), 1),
(2, 1, to_date('01-02-2019', 'dd-mm-yyyy'), 2),
(3, 1, to_date('01-03-2019', 'dd-mm-yyyy'), 3),
(4, 2, to_date('01-01-2019', 'dd-mm-yyyy'), 4),
(5, 2, to_date('01-02-2019', 'dd-mm-yyyy'), 5),
(6, 2, to_date('01-03-2019', 'dd-mm-yyyy'), 6);

create table role_rates (id int, role_id integer, start_date date, rate numeric);
insert into role_rates
values
(1, 2, to_date('01-01-2019', 'dd-mm-yyyy'), 7),
(2, 2, to_date('01-02-2019', 'dd-mm-yyyy'), 8),
(3, 2, to_date('01-03-2019', 'dd-mm-yyyy'), 9);

create table staff (id int, role_id integer);
insert into staff
values
(1, 1),
(2, 2);

SELECT 
    t.staff_id, 
    t.entry_date, 
    t.hours * (case when rr.rate > 0 then rr.rate else sr.rate end) AS total_rate 
FROM   time_entries t
LEFT   JOIN LATERAL (
   SELECT sr.rate
   FROM   staff_rates sr
   WHERE  sr.staff_id = t.staff_id
   AND    sr.start_date <= t.entry_date -- "on or before that time entry"
   ORDER  BY sr.start_date DESC NULLS LAST
   LIMIT  1
   ) sr ON true
LEFT   JOIN LATERAL (
   SELECT staff.id, staff.role_id
   FROM   staff, staff_rates sr
   WHERE  sr.staff_id = staff.id
   LIMIT  1
   ) staff ON true
LEFT   JOIN LATERAL (
   SELECT rr.rate
   FROM   role_rates rr, staff, staff_rates sr
   WHERE  rr.role_id = staff.role_id AND sr.staff_id = staff.id
   AND    rr.start_date <= t.entry_date -- "on or before that time entry"
   ORDER  BY rr.start_date DESC NULLS LAST
   LIMIT  1
   ) rr ON true;

How do I join the role rates table to get the following result when the new rates are introduced?

staff_id | entry_date | total_rate
--------------------------------------------
1        | 15-01-2019 | 1 -- uses staff rate as the staff's role has no rates
1        | 15-02-2019 | 4 -- uses staff rate as the staff's role has no rates
1        | 15-03-2019 | 9 -- uses staff rate as the staff's role has no rates
2        | 15-01-2019 | 28 -- uses role rate as the staff's role has rates
2        | 15-02-2019 | 40 -- uses role rate as the staff's role has rates
2        | 15-03-2019 | 54 -- uses role rate as the staff's role has rates
`

Best Answer

If I understand correctly, I would do the following.

1) start with the time_entries

select t.id, t.staff_id, t.entry_date, t.hours 
from time_entries t;

2) add role_id to this query:

select 
  t.id, t.staff_id, t.entry_date, t.hours, 
  s.role_id 
from time_entries t 
inner join staff s on s.id=t.staff_id;

3) add rate:

select 
    t.id, 
    t.staff_id, 
    t.entry_date, 
    t.hours, 
    s.role_id,
    r.*
from time_entries t 
inner join staff s on s.id=t.staff_id
left join lateral (select rr.role_id, max(rr.rate)
    from role_rates rr
    where rr.role_id=s.role_id and rr.start_date<=t.entry_date
    group by rr.role_id) r on r.role_id = s.role_id
;

output of this (I did read the note of Phil, and I guessed an answer ?)

 id | staff_id | entry_date | hours | role_id | role_id | max
----+----------+------------+-------+---------+---------+-----
  1 |        1 | 2019-01-15 |     1 |       1 |         |
  2 |        1 | 2019-02-15 |     2 |       1 |         |
  3 |        1 | 2019-03-15 |     3 |       1 |         |
  4 |        2 | 2019-01-15 |     4 |       2 |       2 |   7
  5 |        2 | 2019-02-15 |     5 |       2 |       2 |   8
  6 |        2 | 2019-03-15 |     6 |       2 |       2 |   9
(6 rows)

I do not have rates for role 1 because I do not have values for role_id=1 in role_rates.

Related Question