Stack Overflow Asked by Beefstu on November 12, 2021
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.
If there are no emp_attendance records for an employee don’t add a empty row. In my example though employee 2 & 3 exist there should be no output for them.
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'));
You can get the Monday for a week with the trunc() function and the ISO week 'IW' element; and then group by that as well as your other fields:
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
To get the output you want you can manipulate that truncated date, and add a row_number() for the group ID:
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
db<>fiddle for the current data; and for the original data.
Answered by Alex Poole on November 12, 2021
In the database structure above you can use column start_date in table emp_attendance to calculate the current week number then to group according it.
you can use additional parameter with round function round (column_name, round_num) use negative values with the second paramenter
-1 means round to 10s
-2 means round to 100s
-3 means round to 1000s
and so on ...
the query should be something like the following:
select to_char(a.start_date, 'WW') weekNumber, 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), -1) rounded_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 ,to_char(a.start_date, 'WW')
-- order by first column = weekNumber then by other columns
order by 1, e.employee_id, e.first_name, e.last_name;
Answered by Eng. Samer T on November 12, 2021
There are many posts with similar topics so I will link them as I go->
Answered by Nik Srinivas on November 12, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP