Grouping data by week

Question

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'));

It would be helpful to include the expected rsult for your data )and preferably a tabular representation of the raw data, as well as the insert statements). Do you want one total for each week; or one per employee per week? (And what if an employee has no time in a given week - show as zero?) If the start date is on Sunday and the end date is on Monday, which week does that fall into? Or do you want to split that row across both weeks, with the appropriate amount of time in each? — Alex Poole, Jul 21 '20 at 22:26
@Alex I know its been a while but I am finally getting back to this project. In your solution below week numbers will circle around every year in order to prevent the sum of same week for every year can I SUM on YYYY concatenatd with week_number from the start date. Therefore giving me a static SUM and different row if run for multiple years? — Beefstu, Aug 01 '21 at 11:15
`trunc(...., 'IW')` retains the year so you don't need to do anything else. [Week 12 appears twice here](http://sqlfiddle.com/#!4/67b4f/1), for different years, and with different actual dates. If you're displaying the week number then you can get that, and the year, from the truncated value after aggregating. — Alex Poole, Aug 01 '21 at 22:21

score 2 · Answer 1 · answered Jul 21 '20 at 22:58

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;

Nik Srinivas · Answer 2 · 2020-07-21T22:25:52.547

1

There are many posts with similar topics so I will link them as I go->

This post can help you group data by week and do grouped calculation Oracle SQL - Sum and group data by week
You can then use the mySQL ROUND() function and set the parameters to round to the nearest 10's place. Documentation found here https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm

edited Jul 21 '20 at 22:25

answered Jul 21 '20 at 22:03

Nik Srinivas

163
5

Thanks but these commands are sql server specific and I'm running Oracle – Beefstu Jul 21 '20 at 22:12

score 1 · Answer 3 · answered Jul 22 '20 at 21:44

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.

perfect. Thank you for your expertise and detailed explanation — Beefstu, Jul 23 '20 at 09:16

Grouping data by week

3 Answers3