2

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

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.

  • Thanks - it's not quite the result I was after but I figured it out in the end – Ryan King May 11 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 at 14:17
0

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 at 14:46
  • I'll find out within the next week. Do you think it could be more performant? – Ryan King May 11 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 at 15:11
  • I'd love to see another solution if you come up with one – Ryan King May 11 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 at 16:04
0

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 at 0:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.