MySQL: calculate overlapping periods, MySQL

Language: Layout:

DROP TABLE IF EXISTS ort_calendar;
DROP TABLE IF EXISTS ort_rule;
DROP TABLE IF EXISTS shifts;

CREATE TABLE shifts (
  shift_id        int(10) unsigned                          NOT NULL AUTO_INCREMENT,
  user_name       varchar(20) COLLATE utf8_unicode_ci   DEFAULT NULL,
  date_start      datetime                                  NOT NULL,
  date_end        datetime                                  NOT NULL,
  pause           tinyint(3)                            DEFAULT NULL,

PRIMARY KEY (shift_id),
  KEY idx_date_start (date_start),
  KEY idx_date_end (date_end)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table shifts */

insert into shifts (shift_id,user_name,date_start,date_end,pause) values 
(1,'Franck' ,'2014-12-08 08:00:00','2014-12-08 17:00:00',30),
(2,'Kevin'  ,'2014-12-09 15:00:00','2014-12-10 00:00:00',60),
(3,'Michael','2014-12-10 08:00:00','2014-12-10 17:00:00',60),
(4,'Franck' ,'2014-12-31 08:00:00','2014-12-31 17:00:00',90),
(5,'Franck' ,'2015-01-01 08:00:00','2015-01-01 17:00:00',30),
(6,'Franck' ,'2014-12-07 08:00:00','2014-12-07 17:00:00',30),
(7,'Michael','2014-12-09 15:00:00','2014-12-10 00:30:00',30),
(8,'Derrick','2014-12-09 15:00:00','2014-12-10 06:12:27',30);

/******************************/

CREATE TABLE ort_rule (
  id int(10)     unsigned                     NOT NULL AUTO_INCREMENT,
  ort_name       varchar(80)                  NOT NULL,
  ort_type       enum('daily','yearly')       NOT NULL,
  time_start     time                         NOT NULL,
  time_end       time                         NOT NULL,
  factor         double(5,2)                  NOT NULL,
  deleted_by     int(10) unsigned         DEFAULT NULL,
  deleted_date   datetime                 DEFAULT NULL,

PRIMARY KEY (id),
  KEY ort_name_idx (ort_name)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into ort_rule(id,ort_name,ort_type,time_start,time_end,factor,deleted_by,deleted_date) values

(1,'Overtime evening'    ,'daily','18:00:00','23:59:59',1.20,NULL,NULL),
(2,'Overtime Night'      ,'daily','00:00:00','05:00:00',1.20,NULL,NULL),
(3,'Overtime Night Extra','daily','01:00:00','03:00:00',1.30,NULL,NULL);

/******************************/

CREATE TABLE ort_calendar (
  id              int(10) unsigned        NOT NULL AUTO_INCREMENT,
  ort_rule_id     int(10) unsigned        NOT NULL,
  ort_date_start  datetime            DEFAULT NULL,
  ort_date_end    datetime            DEFAULT NULL,

PRIMARY KEY (id),
  UNIQUE KEY ort_calendar_unique (ort_rule_id,ort_date_start,ort_date_end),
  KEY ort_date_start_idx (ort_date_start),
  KEY ort_date_end_idx (ort_date_end),
  CONSTRAINT ort_period_ort_rule_fk FOREIGN KEY (ort_rule_id) REFERENCES ort_rule (id) ON DELETE CASCADE ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=783 DEFAULT CHARSET=utf8;

/*Data for the table ort_calendar */

insert into ort_calendar (id,ort_rule_id,ort_date_start,ort_date_end) values

(  6,1,'2014-12-08 18:00:00','2014-12-08 23:59:59'),
( 58,1,'2014-12-09 18:00:00','2014-12-09 23:59:59'),
(110,1,'2014-12-10 18:00:00','2014-12-10 23:59:59'),
(266,2,'2014-12-09 00:00:00','2014-12-09 05:00:00'),
(318,2,'2014-12-10 00:00:00','2014-12-10 05:00:00'),
(527,3,'2014-12-09 01:00:00','2014-12-09 03:00:00'),
(579,3,'2014-12-10 01:00:00','2014-12-10 03:00:00');

/******************************

for shifts that span midnight, break into 2 separate shifts:
    1) date_start up to 23:59:59
    2) 'tomorrow' from 00:00:00 to date_end
    
split datetimes into separate date/time parts to make
time comparisons a little easier later on

drop table if exists t_scratch;

create temporary table if not exists t_scratch as
(
select	shift_id         as id,
		date(date_start) as date_start,
		time(date_start) as time_start,
		date(date_end)   as date_end,
		time(date_end)   as time_end
from	shifts
where	date(date_start) = date(date_end)
);

insert into t_scratch (id, date_start, time_start, date_end, time_end)
select  shift_id,
		date(date_start),
		time(date_start),
		date(date_start),
		cast('23:59:59' as time)
from	shifts
where	date(date_start) != date(date_end);

insert into t_scratch (id, date_start, time_start, date_end, time_end)
select  shift_id,
		date(date_end),
		cast('00:00:00' as time),
		date(date_end),
		time(date_end)
from	shifts
where	date(date_start) != date(date_end);

drop table if exists t_shifts;

create temporary table if not exists t_shifts as
(select distinct * from t_scratch);

drop table t_scratch;

/******************************

map our new set of shifts to all ort_rules that apply;
don't worry about overlaps at this point in time

drop table if exists t_shifts_rules;

create temporary table if not exists t_shifts_rules as
(
select  s.id         as s_id,
        s.date_start as s_date_start,
        s.time_start as s_time_start,
        s.date_end   as s_date_end,
        s.time_end   as s_time_end,
        r.time_start as r_time_start,
        r.time_end   as r_time_end
from    t_shifts s
join    ort_rule r

on      (s.time_start <= r.time_start and s.time_end   >= r.time_end)
or      (s.time_start <= r.time_start and s.time_end   >= r.time_start)
or      (s.time_start >= r.time_start and s.time_start <= r.time_end)
);

/******************************

get a list of all ort_rule times; make 2 copies of the
resulting table so  we can perform a self join in the
block of code

drop table if exists t_scratch;

create temporary table if not exists t_scratch as
(select time_start as t_time from ort_rule);

insert into t_scratch (t_time)
select time_end from ort_rule;

drop table if exists t_times1;

create temporary table if not exists t_times1 as
(select distinct * from t_scratch);

drop table if exists t_scratch;

drop table if exists t_times2;

create temporary table if not exists t_times2 as
(
select * from t_times1
);

/******************************

perform the 'self' joins of t_time[12] to generate
a set of all possible rule start/end time ranges; some
of these will be invalid but that's ok, we'll use some
additional logic later to make sure we only access the
ones that make sense

drop table if exists t_time_ranges;

create temporary table if not exists t_time_ranges as
(select distinct
        t1.t_time as time_start,
        t2.t_time as time_end
from    t_times1 t1
join    t_times2 t2
on      t1.t_time < t2.t_time
);

/*
drop table if exists t_times1;
drop table if exists t_times2;
*/

/******************************

and now for the main event

for overlap calculations we need to concatentate our
separate dates/times back into datetime values for

select  or1.id        as ort_rule_id,
        or1.factor,
        s.user_name,
        s.shift_id,
        s.date_start  as shift_start,
        s.date_end    as shift_end,
        
        round( (timestampdiff(second, s.date_start, s.date_end) / 3600), 2)                   as shift_duration_hours,
        greatest(cast(concat(dt.s_date_start,' ',dt.time_start) as datetime), s.date_start)   as overlap_start,
        least(   cast(concat(dt.s_date_end  ,' ',dt.time_end)   as datetime), s.date_end)     as overlap_end,

round( (timestampdiff(second, greatest(cast(concat(dt.s_date_start,' ',dt.time_start) as datetime), s.date_start), 
                                      least(   cast(concat(dt.s_date_end  ,' ',dt.time_end)   as datetime), s.date_end)
                ) / 3600), 2
             )  as overlap_duration_hours
from
(
select  /* shift data */
        tsr.s_id, 
        tsr.s_date_start, 
        tsr.s_time_start, 
        tsr.s_date_end, 
        tsr.s_time_end, 
        /* rule data */
        ttr.time_start, 
        min(ttr.time_end) as time_end

from    t_shifts_rules tsr
left
join    t_time_ranges ttr

on      tsr.r_time_start <= ttr.time_start
and     tsr.r_time_end   >= ttr.time_end

and     tsr.s_time_end   >= ttr.time_start

group by tsr.s_id, tsr.s_date_start, tsr.s_time_start, tsr.s_date_end, tsr.s_time_end, ttr.time_start
) dt

join    shifts s
on      s.shift_id = dt.s_id

join    ort_calendar oc 
on      s.date_end   > oc.ort_date_start 
and     s.date_start < oc.ort_date_end

join    ort_rule or1
on      or1.id         = oc.ort_rule_id
and     dt.time_start >= or1.time_start
and     dt.time_end   <= or1.time_end
and     or1.factor = (select max(or2.factor)
                      from   ort_rule or2
		      where  dt.time_start >= or2.time_start
		      and    dt.time_end   <= or2.time_end)

order by s.user_name   asc,
         s.date_start  asc,
         overlap_start asc,
         or1.factor    desc;


absolute service time: 0,43 sec