0

I have a table of EMPLOYEES that contains information about the DATE and WORKTIME per that day. Fx:

ID    |    DATE      |     WORKTIME    |
----------------------------------------
1     |  1-Sep-2014  |      4          |
2     |  2-Sep-2014  |      6          |
1     |  3-Sep-2014  |      5.5        |
1     |  4-Sep-2014  |      7          |
2     |  4-Sep-2014  |      4          |
1     |  9-Sep-2014  |      8          |

and so on.

Question: How can I create a query that would allow me to calculate amount of time worked per week (HOURS_PERWEEK). I understand that I need a summation of WORKTIME together with grouping considering both, ID and week, but so far my trials as well as googling didnt yield any results. Any ideas on this? Thank you in advance!

edit:

Got a solution of

select id, sum (worktime), trunc(date, 'IW') week 
from employees  
group by id, TRUNC(date, 'IW');

But will need somehow to connect that particular output with DATE table by updating a newly created column such as WEEKLY_TIME. Any hints on that?

grievo
  • 3
  • 3

2 Answers2

0

You can find the start of the ISO week, which will always be a Monday, using TRUNC("DATE", 'IW').

So if, in the query, you GROUP BY the id and the start of the week TRUNC("DATE", 'IW') then you can SELECT the id and aggregate to find the SUM the WORKTIME column for each id.

Since this appears to be a homework question and you haven't attempted a query, I'll leave it at this to point you in the correct direction and you can complete the query.

Update

Now I need to create another column (lets call it WEEKLY_TIME) and populate it with values from the current output, so that Sep 1,3,4 (for ID=1) would all contain value 16.5, specifying that on that day (that is within the certain week) that person worked 16.5 in total. And for ID=2 it would then be a value of 10 for both Sep 2 and 4.

For this, if I understand correctly, you appear to not want to use aggregation functions and want to use the analytic version of the function:

select id,
       "DATE",
       trunc("DATE", 'IW') week,
       worktime,
       sum (worktime) OVER (PARTITION BY id, trunc("DATE", 'IW'))
         AS weekly_time
from   employees;

Which, for the sample data:

CREATE TABLE employees (ID, "DATE", WORKTIME) AS
SELECT 1, DATE '2014-09-01', 4   FROM DUAL UNION ALL
SELECT 2, DATE '2014-09-02', 6   FROM DUAL UNION ALL
SELECT 1, DATE '2014-09-03', 5.5 FROM DUAL UNION ALL
SELECT 1, DATE '2014-09-04', 7   FROM DUAL UNION ALL
SELECT 2, DATE '2014-09-04', 4   FROM DUAL UNION ALL
SELECT 1, DATE '2014-09-09', 8   FROM DUAL;

Outputs:

ID DATE WEEK WORKTIME WEEKLY_TIME
1 2014-09-01 00:00:00 2014-09-01 00:00:00 4 16.5
1 2014-09-03 00:00:00 2014-09-01 00:00:00 5.5 16.5
1 2014-09-04 00:00:00 2014-09-01 00:00:00 7 16.5
1 2014-09-09 00:00:00 2014-09-08 00:00:00 8 8
2 2014-09-04 00:00:00 2014-09-01 00:00:00 4 10
2 2014-09-02 00:00:00 2014-09-01 00:00:00 6 10

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Sorry, I didnt think that failed attempts would be of any use. Following @Brandon_R answer, I ended up with a query `select id, sum (worktime), trunc(date, 'IW') week from employees group by id, TRUNC(date, 'IW');` but the WEEK table now shows the beginning of the week. Could you give any hints on how could I now connect those values with newly created column such as WEEKLY_TIME? So that DATE would have the date itself and WEEKLY_TIME would store calculated and grouped value for that week? – grievo Feb 14 '22 at 17:53
  • @grievo This [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=39492c0cc1207ba61ea48a679674d143) is what you have got so far and I'm not entire sure what else you are expecting as output. – MT0 Feb 14 '22 at 19:03
  • Yes, that's the result I got. Now I need to create another column (lets call it WEEKLY_TIME) and populate it with values from the current output, so that Sep 1,3,4 (for ID=1) would all contain value 16.5, specifying that on that day (that is within the certain week) that person worked 16.5 in total. And for ID=2 it would then be a value of 10 for both Sep 2 and 4. Hope I explained it clearly :) – grievo Feb 14 '22 at 19:11
  • That is exactly what I needed. Thank you so much for your assistance! – grievo Feb 14 '22 at 20:05
0

edit: answer submitted without noticing "Oracle" tag. Otherwise, question answered here: Oracle SQL - Sum and group data by week

Select employee_Id,
DATEPART(week, workday) as [Week],
sum (worktime) as [Weekly Hours]
from WORK
group by employee_id, DATEPART(week, workday)

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=238b229156a383fa3c466b6c3c2dee1e

Brandon_R
  • 46
  • 5