Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
Create table employees(
 employee_id Number(6),
 first_name VARCHAR2(20),
 last_name VARCHAR2(20),
 hourly_rate NUMBER
);
INSERT INTO employees(
 employee_id,
 first_name,
 last_name,
 hourly_rate)
VALUES 
(1, 'John', 'Doe', 55.05);
1 rows affected

INSERT INTO employees(
 employee_id,
 first_name,
 last_name,
 hourly_rate)
VALUES 
(2, 'Jane', 'Smith', 45.50);
1 rows affected

INSERT INTO employees(
 employee_id,
 first_name,
 last_name,
 hourly_rate)
VALUES 
(3, 'Mike', 'Jones', 25.00);
1 rows affected

create table emp_attendance(
 employee_id NUMBER(6),
 start_date DATE,
end_date DATE
);
INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
TO_DATE('08022020 12:12:12', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('08032020 19:25:19', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
TO_DATE('07212020 07:22:22', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07212020 14:49:13', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
TO_DATE('07222020 08:08:43', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07222020 16:15:55', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
TO_DATE('07232020 08:18:17', 'MMDDYYYY HH24:MI:SS'),

 TO_DATE('07232020 15:58:16', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
TO_DATE('07242020 10:50:15', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07242020 18:21:41', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
TO_DATE('07252020 18:06:11', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07262020 01:34:37', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
 TO_DATE('07262020 10:57:07', 'MMDDYYYY HH24:MI:SS'),

 TO_DATE('07262020 18:44:11', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
TO_DATE('07272020 09:35:44', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07272020 16:14:13', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
TO_DATE('07282020 07:08:31', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07282020 17:17:12', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
 TO_DATE('07292020 05:38:27', 'MMDDYYYY HH24:MI:SS'),

 TO_DATE('07292020 13:06:49', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
TO_DATE('07302020 08:11:51', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07302020 18:29:40', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(1,
TO_DATE('07312020 18:01:51', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('08012020 01:52:37', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(2,
 TO_DATE('08022020 11:12:12', 'MMDDYYYY HH24:MI:SS'),

 TO_DATE('08032020 21:05:11', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO   emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
 (2,
TO_DATE('07212020 05:22:22', 'MMDDYYYY HH24:MI:SS'),

 TO_DATE('07212020 17:19:13', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
 employee_id,
 start_date,
 end_date)
 VALUES 
(2,
  TO_DATE('07222020 06:07:43', 'MMDDYYYY HH24:MI:SS'),

  TO_DATE('07222020 19:45:55', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(2,
 TO_DATE('07232020 08:11:17', 'MMDDYYYY HH24:MI:SS'),

 TO_DATE('07232020 19:18:11', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
  employee_id,
  start_date,
  end_date)
  VALUES 
  (2,
 TO_DATE('07242020 10:10:55', 'MMDDYYYY HH24:MI:SS'),

  TO_DATE('07242020 18:51:44', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(2,
TO_DATE('07252020 18:06:11', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07262020 03:44:37', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(2,
TO_DATE('07262020 11:07:07', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07262020 20:44:11', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(2,
TO_DATE('07272020 06:05:44', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07272020 16:21:13', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(2,
TO_DATE('07282020 07:08:31', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07282020 19:10:12', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(2,
TO_DATE('07292020 05:38:27', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07292020 14:46:49', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(2,
TO_DATE('07302020 08:11:51', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07302020 18:21:54', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(2,
TO_DATE('07312020 18:31:31', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('08012020 04:52:37', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(3,
TO_DATE('07282020 07:08:31', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07282020 19:10:12', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(3,
TO_DATE('07292020 05:38:27', 'MMDDYYYY HH24:MI:SS'),

 TO_DATE('07292020 14:46:49', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(3,
TO_DATE('07302020 08:11:51', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('07302020 18:21:54', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(3,
TO_DATE('07312020 18:31:31', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('08012020 04:52:37', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

INSERT INTO emp_attendance(
employee_id,
start_date,
end_date)
VALUES 
(3,
 TO_DATE('08022020 11:12:12', 'MMDDYYYY HH24:MI:SS'),

TO_DATE('08032020 21:05:11', 'MMDDYYYY HH24:MI:SS'));
1 rows affected

select     e.employee_id,            e.first_name,         e.last_name,
 trunc(sum(a.end_date - a.start_date) * 24) hours, 
trunc(mod(sum(a.end_date - a.start_date) * 24 * 60,60)) minutes,          
round(mod(sum(a.end_date - a.start_date) * 24 * 60 * 60,60)) seconds
from employees e
join emp_attendance a on a.employee_id = e.employee_id
group by e.employee_id, e.first_name, e.last_name
order by e.employee_id, e.first_name, e.last_name;


EMPLOYEE_ID
FIRST_NAME
LAST_NAME
HOURS
MINUTES
SECONDS


1
John
Doe
119
38
12

2
Jane
Smith
150
27
56

3
Mike
Jones
75
34
11


select e.employee_id,
  e.first_name,
  e.last_name,
  trunc(a.start_date, 'IW') as week,
  trunc(sum(a.end_date - a.start_date) * 24) hours,
  trunc(mod(sum(a.end_date - a.start_date) * 24 * 60,60)) minutes,
  round(mod(sum(a.end_date - a.start_date) * 24 * 60 * 60,60)) seconds
from employees e
join emp_attendance a on a.employee_id = e.employee_id
group by e.employee_id, e.first_name, e.last_name, trunc(a.start_date, 'IW')
order by e.employee_id, e.first_name, e.last_name, trunc(a.start_date, 'IW');


EMPLOYEE_ID
FIRST_NAME
LAST_NAME
WEEK
HOURS
MINUTES
SECONDS


1
John
Doe
07202020 00:00:00
46
0
58

1
John
Doe
07272020 00:00:00
73
37
14

2
Jane
Smith
07202020 00:00:00
64
38
16

2
Jane
Smith
07272020 00:00:00
85
49
40

3
Mike
Jones
07272020 00:00:00
75
34
11


select row_number() over (order by trunc(a.start_date, 'IW')) as group_id,
  e.employee_id,
  e.first_name,
  e.last_name,
  trunc(sum(a.end_date - a.start_date) * 24) hours,
  trunc(mod(sum(a.end_date - a.start_date) * 24 * 60,60)) minutes,
  round(mod(sum(a.end_date - a.start_date) * 24 * 60 * 60,60)) seconds,
  to_char(trunc(a.start_date, 'IW'), 'MMDDYYYY') as start_date,
  to_char(trunc(a.start_date, 'IW') + 6, 'MMDDYYYY') as end_date
from employees e
join emp_attendance a on a.employee_id = e.employee_id
group by e.employee_id, e.first_name, e.last_name, trunc(a.start_date, 'IW')
order by e.employee_id, e.first_name, e.last_name;


GROUP_ID
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
HOURS
MINUTES
SECONDS
START_DATE
END_DATE


3
1
John
Doe
73
37
14
07272020
08022020

2
1
John
Doe
46
0
58
07202020
07262020

5
2
Jane
Smith
85
49
40
07272020
08022020

1
2
Jane
Smith
64
38
16
07202020
07262020

4
3
Mike
Jones
75
34
11
07272020
08022020
EMPLOYEE_ID	FIRST_NAME	LAST_NAME	WEEK	HOURS	MINUTES	SECONDS
1	John	Doe	07202020 00:00:00	46	0	58
1	John	Doe	07272020 00:00:00	73	37	14
2	Jane	Smith	07202020 00:00:00	64	38	16
2	Jane	Smith	07272020 00:00:00	85	49	40
3	Mike	Jones	07272020 00:00:00	75	34	11
GROUP_ID	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	HOURS	MINUTES	SECONDS	START_DATE	END_DATE
3	1	John	Doe	73	37	14	07272020	08022020
2	1	John	Doe	46	0	58	07202020	07262020
5	2	Jane	Smith	85	49	40	07272020	08022020
1	2	Jane	Smith	64	38	16	07202020	07262020
4	3	Mike	Jones	75	34	11	07272020	08022020