, 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);
--------+--------+-------+---------+----------
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
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.
