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('08032020 12:12:12', 'MMDDYYYY HH24:MI:SS'),

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

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

  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,       emp_attendance a

   where 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
128
51
19


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

1
John
Doe
08032020 00:00:00
9
13
7


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


1
1
John
Doe
46
0
58
07202020
07262020

3
1
John
Doe
9
13
7
08032020
08092020

2
1
John
Doe
73
37
14
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
1	John	Doe	08032020 00:00:00	9	13	7
GROUP_ID	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	HOURS	MINUTES	SECONDS	START_DATE	END_DATE
1	1	John	Doe	46	0	58	07202020	07262020
3	1	John	Doe	9	13	7	08032020	08092020
2	1	John	Doe	73	37	14	07272020	08022020