HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

MySQL calculate overlapping period excluding overlapping rules

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqlstackoverflowmysqldbadatetime
periodexcludingmysqloverlappingcalculaterules

Problem

I would like to calculate the period overlap in hours based on special rules.
I created a test setup, that works as long as the rules themselves don't overlap.

Example non overlapping rules:

id  ort_name          ort_type  time_start  time_end  factor  
------  ----------------  --------  ----------  --------  --------
     1  Overtime evening  daily     18:00:00    23:59:59      1.20
     2  Overtime Night    daily     00:00:00    05:00:00      1.20


When the rules do overlap I need to solve this in our programming language, but I was looking for a database only solution.

Example overlapping rules:

id  ort_name              ort_type  time_start  time_end  factor  
------  --------------------  --------  ----------  --------  --------
     1  Overtime evening      daily     18:00:00    23:59:59      1.20
     2  Overtime Night        daily     00:00:00    05:00:00      1.20
     3  Overtime Night Extra  daily     01:00:00    03:00:00      1.30


In the below example the users Kevin and Michael don't have overlapping rules and the result is as expected. The user Derrick is affected by rule (id) 1, 2 and 3 (overlapping rules)

Example query:

SELECT
    OC.ort_rule_id
    , ORT.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(OC.ort_date_start, S.date_start) AS overlap_start
    , LEAST(OC.ort_date_end, S.date_end) AS overlap_end
    , ROUND((TIMESTAMPDIFF(SECOND, GREATEST(OC.ort_date_start, S.date_start), LEAST(OC.ort_date_end, S.date_end)) / 3600), 2) AS overlap_duration_hours 
FROM
    shifts AS S    
INNER JOIN ort_calendar AS OC 
        ON S.date_end > OC.ort_date_start 
        AND S.date_start = '2014-12-01 00:00:00'
AND
    S.date_end < '2014-12-01 00:00:00' + INTERVAL 1 MONTH
ORDER BY
    S.user_name ASC, S.date_start ASC, overlap_start ASC, ORT.factor DESC


Output:

Solution

We'll start with a few assumptions/observations/limits ...

  • ort_rules time periods do not span midnight (at least for ort_type = 'daily')



  • ort_calendar time periods do not span midnight



  • shifts time periods can span midnight but at most once (ie, person isn't working from day1 to day2 to day3)



  • completely ignoring the ort_rule.ort_type = {daily | yearly } as we've only got sample 'daily' data to work with



  • 'daily' rules apply for all 7 days of the week (ie, weekends are treated just like weekdays)



  • don't know what version of MySQL is in use so will skip trying to do this with CTEs (not available prior to MySQL 8.0) and instead use some temp tables



  • conducted my testing on 2 different fiddles, with 2 different versions of MySQL; one fiddle allows create temp table/select/union/select while the other doesn't allow the union so had to use create temp table/insert-select



  • while a lot of this code could be included in the main query (as derived tables and/or CTEs), I've opted to use temp tables in order to allow for easier debugging



Data setups:

  • copied the provided DDL, moved the create/drop commands around some to get around the 'you cannot drop a table with active FK constraints')



Build a new list of shifts:

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

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'
and     date_end         = '2014-12-01 00:00:00'
and     date_end         <= '2014-12-01 00:00:00' + interval 1 month;


A key part of this solution is next ... breaking down ort_rule time ranges into non-overlapping time ranges.

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;


And now the main event:

```
/

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 /

Code Snippets

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

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;


ort_rule_id  factor  user_name  shift_id  shift_start          shift_end            shift_duration_hours  overlap_start        overlap_end          overlap_duration_hours
-----------  ------  ---------  --------  -------------------  -------------------  --------------------  -------------------  -------------------  ----------------------
1            1.2     Derrick    8         2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-09 18:00:00  2014-12-09 23:59:59  6.00
2            1.2     Derrick    8         2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-10 00:00:00  2014-12-10 01:00:00  1.00
3            1.3     Derrick    8         2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-10 01:00:00  2014-12-10 03:00:00  2.00
2            1.2     Derrick    8         2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-10 03:00:00  20

Context

StackExchange Database Administrators Q#85929, answer score: 2

Revisions (0)

No revisions yet.