I'm looking for a way to group the following emp_attendance rows below into weeks MON - SUN.
I saw something on the internet about a 'IW' format but I don't think that will allow me to group, perhaps I'm doing something wrong. In addition, dates such as 12312020 Thursday and 01012021 Friday should be grouped together as they fall between a Monday and a Sunday yet they are different years.
My current code is summing all the data and producing the following output
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
I'm looking to produce this output. Note for every row in the group I need the SAME group_id and a start_date the MONDAY of the group and end_date the Sunday of the group so I can back track where the data came from if needed.
Thanks to all who answer
GROUP_ID
EMPLOYEE_ID FIRST_NAME LAST_NAME HOURS MINUTES SECONDS START_DATE END_DATE
1 1 John Doe 46 0 58 07202020 07262020
2 1 John Doe 73 37 14 07272020 08022020
3 1 John Doe 9 13 7 08032020 08082020
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);
INSERT INTO employees(
employee_id,
first_name,
last_name,
hourly_rate)
VALUES
(2, 'Jane', 'Smith', 45.50);
INSERT INTO employees(
employee_id,
first_name,
last_name,
hourly_rate)
VALUES
(3, 'Mike', 'Jones', 25.00);
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));
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'));