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:

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.

CodePudding user response：

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

CodePudding user response：

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:

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