Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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 */ 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;
run
|
edit
|
history
|
help
0
vwall
test
test
ritesh
list with indented categories
empresa
Point System Alpha.1
delivery_boy
new_road
Creation tables