how to use mysql/sql to remove the overlapping durations among multiple events?

Question

Below is a record table, which contains different events info. How to get the duration of sum(eventB + eventC + eventD) - eventC - 2*eventD within the same date. (Refer below table)

NOTES:

1. assume eventB only happen once a day, and there will be always one eventC and two eventD happen during the eventB
2. there can be multiple eventC, eventD within a day
3. need to calculate the: sum of the duration of (eventB + eventC + eventD) then minus the 1 eventC and 2 eventD duration, which happen between starttime and endtime of eventB.

table and MySQL details feel free to edit it

Basically, need to handle the overlap durations among eventC, enentD and eventB

Can someone please provide me with some code to handle the above case? Thanks a lot

Sample record table:

eventDate	event	eventStart	eventEnd	durations
2020-02-01	eventB	2020-02-01 09:10:25	2020-02-01 09:50:25	40
2020-02-01	eventD	2020-02-01 09:15:25	2020-02-01 09:20:25	5
2020-02-01	eventC	2020-02-01 09:30:25	2020-02-01 09:35:25	5
2020-02-01	eventD	2020-02-01 09:40:25	2020-02-01 09:45:25	5
2020-02-01	eventC	2020-02-01 09:55:25	2020-02-01 09:59:25	4

output for date 2020-02-01 will be 44 -->

sum(B + C + D) - C - 2*D = (40) + (5 + 4) + (5 + 5 ) - 5 - 5 -5 = 44

40 : sum of eventB durations

(5+4) is the sum of event C and so on,

-5 , - 5 and -5 is because the one eventC and two eventD happen during the eventB period.

Answer 1

To get all overlapping events, you can do:

select r1.*, r2.event "OverlapsWith"
from record r1
inner join record r2 on r1.eventStart between r2.eventStart and r2.eventEnd

This wil list something like this:

eventDate	event	eventStart	eventEnd	durations	OverlapsWith
2020-02-01	eventA	2020-02-01 08:05:25	2020-02-01 08:07:25	2	eventA
2020-02-01	eventA	2020-02-01 08:15:25	2020-02-01 08:20:25	5	eventA
2020-02-01	eventD	2020-02-01 09:40:25	2020-02-01 09:45:25	5	eventB
2020-02-01	eventC	2020-02-01 09:30:25	2020-02-01 09:35:25	5	eventB

EDIT: The correct query should have been:

select r1.*, r2.event "OverlapsWith"
from record r1
inner join record r2 on r1.eventStart <= r2.eventEnd
                     and r1.eventEnd >= r2.eventStart 

Because when some event starts and ends during another event, the first query would not select it.

DBFIDDLE

Answer 2

WITH RECURSIVE
-- enumerate rows within the date ordering by event start
cte1 AS ( SELECT eventDate,
                 eventStart,
                 eventEnd,
                 ROW_NUMBER() OVER (PARTITION BY eventDate ORDER BY eventStart) rn
          FROM record
),
-- process rows, combine previous and current ranges into one range
cte2 AS ( SELECT eventDate,
                 rn, 
                 eventStart xstart, 
                 eventEnd xend
          FROM cte1 
          WHERE rn = 1
          UNION ALL
          SELECT cte1.eventDate,
                 cte1.rn,
                 CASE WHEN cte2.xend < cte1.eventStart
                      THEN cte1.eventStart
                      ELSE cte2.xstart
                      END,
                 GREATEST(cte1.eventEnd, cte2.xend)
          FROM cte2
          JOIN cte1 ON cte1.rn = cte2.rn + 1
                   AND cte1.eventDate = cte2.eventDate
),
-- remove intermediate ranges
cte3 AS ( SELECT eventDate, xstart, MAX(xend) xend
          FROM cte2
          GROUP BY 1, 2
)
-- get needed data
SELECT eventDate,
       SUM(TIMESTAMPDIFF(MINUTE, xstart, xend)) duration
FROM cte3
GROUP BY 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bf8f354e0a46204ae59246952baf9d87