Re: How to split normal and overtime hours

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
> 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 <kobruleht2(at)hot(dot)ee> 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 <kobruleht2(at)hot(dot)ee> 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.
>>