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
FROM x
WINDOW w AS (PARTITION BY person ORDER BY job_id)
On Sun, Feb 13, 2022 at 1:57 PM Andrus <[email protected]> wrote:
> 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
> in hours table for every person.
>
> Andrus.
> 13.02.2022 14:46 Torsten Förtsch kirjutas:
>
> 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.
>>
>
