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@hot.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@hot.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.
>>