CODEWITHSUNDEEP
mysqlsqldatabaseselectgroup-by
mllamazares
mllamazares

Reputation: 8196

How group the employees in ranges of hours?

I have this table to save the time the employees spend doing a routine task.

CREATE TABLE tasks (
   id INT NOT NULL PRIMARY KEY,
   name VARCHAR(100),
   date_task date,
   time_ini time,
   time_end time
);

This is an example of data:

(1,  "oscar", '2012-01-01', '01:30', '01:32'),
(2,  "oscar", '2012-01-01', '02:30', '02:32'),
(3,  "oscar", '2012-01-01', '05:30', '05:32'),
(4,  "oscar", '2012-01-01', '06:30', '06:32'),

(5,  "mario", '2012-01-01', '02:43', '02:43'),
(6,  "mario", '2012-01-01', '02:53', '02:53'),
(7,  "mario", '2012-01-01', '05:30', '05:30'),

(8, "martah", '2012-01-01', '01:25', '01:28'),
(9, "martah", '2012-01-01', '02:29', '02:41'),

(10, "jesus", '2012-01-01', '01:25', '01:28'),
(11, "jesus", '2012-01-01', '01:25', '02:28'),
(12, "jesus", '2012-01-01', '07:33', '08:32'),
(13, "jesus", '2012-01-01', '07:35', '07:36'),
(14, "jesus", '2012-01-01', '08:36', '08:39'),

(15, "rober", '2012-01-01', '02:43', '02:46'),
(16, "rober", '2012-01-01', '02:56', '03:00'),
(17, "rober", '2012-01-01', '02:29', '11:32'),

(18, "pedro", '2012-01-01', '11:36', '12:46'),
(19, "pedro", '2012-01-01', '12:36', '16:46');

This would be the result:

GROUP       NAME
1           oscar
1           marta
1           jesus
2           mario     
2           rober
3           pedro

I came up with something like this:

select distinct a.name 
from tasks a
where 
    (select count(id) 
    from tasks b 
    where (
          MINUTE(TIMEDIFF(a.time_ini, b.time_ini)) < 15 OR 
          MINUTE(TIMEDIFF(a.time_end, b.time_ini)) < 15
     ) and
    b.name <> a.name) >= 2;

I'm afraid I can't group them this way, but I think I'm not too far to the solution, isn't it?

Any idea, tip or advice will be appreciated, and if you need more info, let me know and I'll edit the post. Is little bit hard to explain...

Upvotes: 1

Views: 119

Answers (1)

Stephan
Stephan

Reputation: 8090

You can try this (although its not in the format you need it.. it should do the job):

SELECT
    a.id as groupId,
    a.name as first,
    b.name as second,
    COUNT(*) as occ
FROM 
    tasks a,task b
WHERE
    b.name <> a.name
    AND a.id > b.id
    AND (
        MINUTE(TIMEDIFF(a.date_ini, b.date_ini)) < 15 OR 
        MINUTE(TIMEDIFF(a.date_end, b.date_ini)) < 15
    )
GROUP BY
    groupId,
    first,
    second

BTW jesus should be in the group with oscar and martha due to records 10 and 11

Upvotes: 1

Related Questions