Oracle 19.8
I have the following test data and I could use some help generating the following output
YEAR 2021
Week 30
1 Smith,Jane HRS 11 MIN 45 SEC 08
2 Doe,John HRS 07 MIN 01 SEC 11
Week Total HRS 18 MIN 46 SEC 19
Week 31
1 Smith,Jane HRS 13 MIN 36 SEC 01
2 Doe,John HRS 03 MIN 27 SEC 00
Week Total HRS 17 MIN 03 SEC 01
Year Total HRS 35 MIN 49 SEC 20
I want to extract every unique YYYY from the emp_attendance table then create totals for YYYY (year total).
Within each YYYY (sample only has 2 week numbers 30,31 but there can be up to 52 per year) I want to create week number totals.
Within each week number I want totals for each employee_id.
I've used the following query to get hours, min, sec but I'm unsure how to roll up the data to produce the several totals I want in my sample output above. I prefer an ALL sql solution.
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;
Emp Fname Lname hrs min sec
1 Jane Smith 25 21 9
2 John Doe 10 28 11
My test case.
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)
);
Insert into employees (employee_id,
first_name,
last_name) values
(1, 'Jane', 'Smith');
Insert into employees (employee_id,
first_name,last_name)
values(2, 'John', 'Doe');
CREATE TABLE emp_attendance(
seq_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
employee_id NUMBER(6),
start_date DATE,
end_date DATE,
week_number NUMBER(2),
create_date DATE DEFAULT SYSDATE
);
INSERT INTO emp_attendance (employee_id, start_date, end_date, week_number)VALUES (1, to_date('20210714 18:35:40','YYYYMMDD HH24:MI:SS'), to_date('20210715 02:34:56','YYYYMMDD HH24:MI:SS'),30);
INSERT INTO emp_attendance (employee_id, start_date, end_date, week_number)VALUES (1, to_date('20210715 19:37:10','YYYYMMDD HH24:MI:SS'), to_date('20210715 23:23:02','YYYYMMDD HH24:MI:SS'),30);
INSERT INTO emp_attendance (employee_id, start_date, end_date, week_number)VALUES (1, to_date('20210722 09:17:11','YYYYMMDD HH24:MI:SS'), to_date('20210722 22:53:12','YYYYMMDD HH24:MI:SS'),31);
INSERT INTO emp_attendance (employee_id, start_date, end_date, week_number)VALUES (2, to_date('20210716 18:24:11','YYYYMMDD HH24:MI:SS'), to_date('20210717 01:25:22','YYYYMMDD HH24:MI:SS'),30);
INSERT INTO emp_attendance (employee_id, start_date, end_date, week_number)VALUES (2, to_date('20210722 09:17:11','YYYYMMDD HH24:MI:SS'), to_date('20210722 12:44:11','YYYYMMDD HH24:MI:SS'),31);