From: | Torsten Förtsch <tfoertsch123(at)gmail(dot)com> |
---|---|
To: | Andrus <kobruleht2(at)hot(dot)ee> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to split normal and overtime hours |
Date: | 2022-02-13 12:46:20 |
Message-ID: | CAKkG4_m_+X2b=Wb7VYV-A+2Dw=93RsgWL9xhWmMsHM5DmkaYmQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2022-02-13 12:57:25 | Re: How to split normal and overtime hours |
Previous Message | Pavel Stehule | 2022-02-13 10:00:00 | Re: Can we go beyond the standard to make Postgres radically better? |