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 column
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 | bi
