Thread
-
How to split normal and overtime hours
Andrus <kobruleht2@hot.ee> — 2022-02-12T22:04:13Z
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.
-
Re: How to split normal and overtime hours
Torsten Förtsch <tfoertsch123@gmail.com> — 2022-02-13T12:46:20Z
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. > -
Re: How to split normal and overtime hours
Andrus <kobruleht2@hot.ee> — 2022-02-13T12:57:25Z
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. >
-
Re: How to split normal and overtime hours
Torsten Förtsch <tfoertsch123@gmail.com> — 2022-02-13T14:46:50Z
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. >> > -
Re: How to split normal and overtime hours
Andrus <kobruleht2@hot.ee> — 2022-02-14T10:04:12Z
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. >>