Hi!
It worked.
Thank you very much.
Andrus.
13.02.2022 16:46 Torsten Förtsch kirjutas:
WITH x AS (
SELECT *
, sum(hours) OVER w AS s
FROM hours
WINDOW w AS (PARTITION BY person ORDER BY job_id)
)
SELECT *
, greatest(least(s, 120) - coalesce(lag(s, 1) OVER w, 0), 0) AS
Hi!
Thank you. In this result, regular and overtime columns contain running
totals.
How to fix this so that those columns contain just hours for each job?
sum on regular column should not be greater than 120 per person.
sum of regular and overtime columns must be same as sum of hours
WITH x AS (
SELECT *
, sum(hours) OVER w AS s
FROM hours
WINDOW w AS (PARTITION BY person ORDER BY job_id)
)
SELECT *
, greatest(least(s, 120) - coalesce(lag(s, 1) OVER w, 0), 0) AS regular
, hours - greatest(least(s, 120) - coalesce(lag(s, 1) OVER w, 0), 0) AS
overtime
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 |
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.
