How to calculate total worktime per week [SQL]-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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

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