MySQL : calculate overlapping periods, MySQL

MySQL : calculate overlapping periods

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

drag along the original shift_id, user_name, date_start/date_end (as dt_start/dt_end) so we don't
	have to join back to shifts later

drop table if exists t_shifts;

create temporary table if not exists t_shifts
(shift_id    int(10) unsigned       not NULL
,user_name   varchar(20)        default NULL
,orig_start  datetime               not NULL
,orig_end    datetime               not NULL
,date_start  date                   not NULL
,time_start  time                   not NULL
,date_end    date                   not NULL
,time_end    time                   not NULL
);

insert into t_shifts (shift_id, user_name, orig_start, orig_end, date_start, time_start, date_end, time_end)

/* shifts that don't span midnight */

select  shift_id,
        user_name,
        date_start,
        date_end,
        date(date_start),
        time(date_start),
        date(date_end),
        time(date_end)
from    shifts
where   date(date_start)  = date(date_end)
and     date_start       >= '2014-12-01 00:00:00'
and     date_end         <= '2014-12-01 00:00:00' + interval 1 month

union

/* shifts that span midnight - create new shift for date_start - 23:59:59 */

select  shift_id,
        user_name,
        date_start,
        date_end,
        date(date_start),
        time(date_start),
        date(date_start),
        cast('23:59:59' as time)
from    shifts
where   date(date_start) != date(date_end)
and     date_start       >= '2014-12-01 00:00:00'
and     date_end         <= '2014-12-01 00:00:00' + interval 1 month

union

/* shifts that span midnight - create new shift for 'tomorrow' from 00:00:00 to date_end */

select  shift_id,
        user_name,
        date_start,
        date_end,
        date(date_end),
        cast('00:00:00' as time),
        date(date_end),
        time(date_end)
from    shifts
where   date(date_start) != date(date_end)
and     date_start       >= '2014-12-01 00:00:00'
and     date_end         <= '2014-12-01 00:00:00' + interval 1 month;

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

drop table if exists t_time_ranges;

create temporary table if not exists t_time_ranges
(time_start    time
,time_end      time
);

insert into t_time_ranges (time_start, time_end)
select r1.time_start,
       r1.time_end
from   ort_rule r1
where  r1.time_start != r1.time_end
and not exists(select 1
               from   ort_rule r2
               where  r2.id != r1.id
               and    (    r2.time_start between r1.time_start and r1.time_end
                       or  r2.time_end   between r1.time_start and r1.time_end
                      ))

union

select r1.time_start,
       r2.time_start
from   ort_rule r1
join   ort_rule r2
on     r2.time_start between r1.time_start and r1.time_end
and    r2.id != r1.id

union

select r1.time_start,
       r2.time_end
from   ort_rule r1
join   ort_rule r2
on     r2.time_end   between r1.time_start and r1.time_end
and    r2.time_start < r1.time_start
and    r2.id != r2.id

union

select r2.time_end,
       r1.time_end
from   ort_rule r1
join   ort_rule r2
on     r2.time_end   between r1.time_start and r1.time_end
and    r2.id != r1.id;

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

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

select  or1.id         as ort_rule_id,
        or1.factor,
        dt.user_name,
        dt.shift_id,
        dt.orig_start  as shift_start,
        dt.orig_end    as shift_end,
        
        round( (timestampdiff(second, dt.orig_start, dt.orig_end) / 3600), 2)                 as shift_duration_hours,
        greatest(cast(concat(dt.date_start,' ',dt.r_time_start) as datetime), dt.orig_start)  as overlap_start,
        least(   cast(concat(dt.date_end  ,' ',dt.r_time_end)   as datetime), dt.orig_end)    as overlap_end,

round( (timestampdiff(second, greatest(cast(concat(dt.date_start,' ',dt.r_time_start) as datetime), dt.orig_start), 
                                      least(   cast(concat(dt.date_end  ,' ',dt.r_time_end)   as datetime), dt.orig_end)
                ) / 3600), 2
             )  as overlap_duration_hours
from
(
select  distinct
        /* shift data */
        ts.shift_id, 
        ts.user_name,
        ts.orig_start,
        ts.orig_end,
        ts.date_start, 
        ts.time_start, 
        ts.date_end, 
        ts.time_end, 
        /* rule/time range data */
        ttr.time_start as r_time_start, 
        ttr.time_end   as r_time_end

from    t_shifts ts
left
join    t_time_ranges ttr

on      ttr.time_start between ts.time_start and ts.time_end
or      ttr.time_end   between ts.time_start and ts.time_end
  
) dt

join    ort_calendar oc 
on      dt.orig_start < oc.ort_date_end
and     dt.orig_end   > oc.ort_date_start

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

order by dt.user_name  asc,
         shift_start   asc,
         overlap_start asc,
         or1.factor    desc;


run \| edit \| history \| help	0