something like
SELECT *
, least(sum(hours) OVER w, 120) AS regular
, greatest(sum(hours) OVER w - 120, 0) AS overtime
FROM hours
WINDOW w AS (PARTITION BY person ORDER BY job_id);
job_id | person | hours | regular | overtime
--------+--------+-------+---------+----------
2 | bill | 10 | 10 | 0
5 | bill | 40 | 50 | 0
8 | bill | 10 | 60 | 0
10 | bill | 70 | 120 | 10
11 | bill | 30 | 120 | 40
13 | bill | 40 | 120 | 80
15 | bill | 10 | 120 | 90
4 | hugo | 70 | 70 | 0
7 | hugo | 130 | 120 | 80
1 | john | 10 | 10 | 0
3 | john | 50 | 60 | 0
6 | john | 30 | 90 | 0
9 | john | 50 | 120 | 20
12 | john | 30 | 120 | 50
14 | john | 50 | 120 | 100
On Sun, Feb 13, 2022 at 12:47 PM Andrus <[email protected]> wrote:
> Hi!
>
> Hours table contains working hours for jobs:
>
> create table hours (
> jobid integer primary key, -- job done, unique for person
> personid char(10) not null, -- person who did job
> hours numeric(5,2) not null -- hours worked for job
> )
>
> Hours more than 120 are overtime hours.
>
> How to split regular and overtime hours into different columns using
> running total by job id and partition by person id?
>
> For example, if John did 3 jobs 1, 2,3 with 90, 50 and 40 hours (total 180
> hours) for each job correspondingly, result table should be:
>
> personid jobid normal_hours overtime_hours
> john 1 90 0
> john 2 30 20
> john 3 0 40
>
> sum on normal_hours column should not be greater than 120 per person.
>
> sum of normal_hours and overtime_hours columns must be same as sum of
> hours column in hours table for every person.
>
> Note that since hours running total becomes greater than 120 in job 2, job
> 2 hours should appear in both hours columns.
>
> Maybe window functions can used.
>
> Andrus.
>
