# Conditionally multiply by relation

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
`
``````

If I understand correctly, I would do the following.

``````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;
``````

``````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.

• Thanks - it's not quite the result I was after but I figured it out in the end – Ryan King May 11 '19 at 14:14
• I ended up using `FROM time_entries t, staff WHERE t.staff_id = staff.id` instead of `inner join staff s on s.id=t.staff_id` - maybe the inner join reads more clearly. – Ryan King May 11 '19 at 14:17

This seems to do the trick

``````SELECT
t.staff_id,
staff.role_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, staff
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 rr.rate
FROM   role_rates rr, staff
WHERE  rr.role_id = staff.role_id AND t.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
WHERE  t.staff_id = staff.id
``````
• I am curious about the performance of this query when there is a whole year (or more) of data in time_entries. – Luuk May 11 '19 at 14:46
• I'll find out within the next week. Do you think it could be more performant? – Ryan King May 11 '19 at 15:00
• i just 'hate' LATERAL', or 'OUTER APPLY' as thery are called in MS-SQL, which is probably the most important reason i have my doubts. (i am thinking about other way to do this a bit more...) – Luuk May 11 '19 at 15:11
• I'd love to see another solution if you come up with one – Ryan King May 11 '19 at 15:43
• Just trying it out on 3.5M entries - it's still processing so I'm not sure how long it takes but I can tell you it's not fast. – Ryan King May 11 '19 at 16:04

Can you try this one:

``````WITH rates AS (
select
sr.staff_id,
sr.start_date,
sr.rate     as StaffRate,
rr.rate     as RoleRate
from staff_rates sr
full outer join role_rates rr on rr.start_date = sr.start_date and rr.role_id = (select role_id from staff where id=sr.staff_id limit 1)
)
SELECT
staff_id,
entry_date,
hours * COALESCE(
(SELECT r2.RoleRate FROM rates r2 WHERE r2.staff_id=te.staff_id AND r2.start_date <= te.entry_date ORDER BY r2.start_date DESC LIMIT 1),
(SELECT r1.StaffRate FROM rates r1 WHERE r1.staff_id=te.staff_id AND r1.start_date <= te.entry_date ORDER BY r1.start_date DESC LIMIT 1)) AS Total_rate
FROM time_entries te
``````
• Thanks, I've done some tests on 300k rows. This one takes 12 mins to execute, the other query takes 7 mins. So lateral join wins this time. – Ryan King May 12 '19 at 0:58