CODEWITHSUNDEEP
mysql
panicos_chr
panicos_chr

Reputation: 47

MySQL query group a time interval by time intervals

Many employees working and I need to find out by an interval of 1 hour the number of employees working. Work schedule is different for employees. Some work 08:00 - 16:00, some 07:00-15:00, some 10:00-22:00. I need a report to show by 1 hour the no_of_employees working.

Hours  08::00- 09:00       No_of_employees working 12,
Hours  09:00-10:00         No_of_employees working 13,
Hours  10:00-11:00         No_of_employees working 14,
etc

I realize that the shorter the interval less the error would be. I have used the code below but no success.

SELECT date-time_from, date-time_to, COUNT(*),
    DATE(DATE_SUB(date-time_from, INTERVAL 1 HOUR))
FROM `employee_roster`
WHERE `work_status` = 'WORK' 
GROUP BY DATE(DATE_SUB(date-time_from, INTERVAL 1 HOUR))

Please find abstract structure of the table as per your request. 

TABLE  `employee_roster` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(10) NOT NULL,
  `date-time_from` datetime  NOT NULL,
  `date-time_to` datetime NOT NULL,
  `work_status` var-char(20) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Date-time_from and Date-time_to are the two datetime fields that make up the working hours(period) i.e 2015/0101 06:00 - 2015/01/01 18:30

Please see output below.

    DatetimeFrom        DatetimeTo         no_of_employee

    2015-01-05 01:00:00 2015-01-05 02:00:00     168
    2015-01-05 06:00:00 2015-01-05 07:00:00     84
    2015-01-05 07:00:00 2015-01-05 08:00:00     84
    2015-01-05 07:20:00 2015-01-05 08:20:00     42
    2015-01-05 19:00:00 2015-01-05 20:00:00     42
    2015-01-05 19:20:00 2015-01-05 20:20:00      84
    2015-01-06 01:00:00 2015-01-06 02:00:00     126
    2015-01-06 06:00:00 2015-01-06 07:00:00      42
    2015-01-06 07:00:00 2015-01-06 08:00:00      42
    2015-01-06 07:20:00 2015-01-06 08:20:00      42

It's strange that it sticks to starting hours of rosters i.e. 07:00, 08:00 even though I have working hours covering the whole 24-hours. It appears to me that the I variable which I replaced by 1 to run the query keeps the condition valid only up to the first record. How can I have an increasing variable i in a sql query?

Upvotes: 2

Views: 497

Answers (1)

rlanvin
rlanvin

Reputation: 6267

There are many ways to go at this problem, and you might want to adapt the queries provided here.

This answer should point you to the right direction.

First, you will need an helper table of every possible work duration (I'm going to assume no more than 12 hours straight, but feel free to adapt)

create table hours (i int unsigned not null);
insert into hours(i) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

Then, using this table, you need to expand your original table so that you get one row for each worked hour for each user. Finally, you want to group by each 1-hour interval. Something like that (again, feel free to adapt, especially if you want to remove the "date" part and only keep the time):

SELECT DATE_ADD(datetime_from, INTERVAL i HOUR) AS start, DATE_ADD(datetime_from, INTERVAL i+1 HOUR) as end, COUNT(employee_id)
FROM employee_roster
JOIN hours ON i <= TIMESTAMPDIFF(HOUR, datetime_from, datetime_to)
GROUP BY CONCAT(start,end);

Upvotes: 1

Related Questions