*Originally Posted by ***SimonJM**

ON (f.dateStart > p.prodStart AND f.dateStart < p.prodEnd)

OR (f.dateEnd > p.prodStart AND f.dateEND < p.prodEnd)

that doesn't quite cover all possibilities

consider the following timeline diagram

fstart = fault start

fend = fault end

pstart = production start

pend = production end

Code:

fstart fend
| |
1 pstart---pend | |
| |
2 pstart--|--pend |
| |
3 | pstart---pend |
| |
4 pstart---|---------------|---pend
| |
5 | pstart--|--pend
| |
6 | | pstart---pend

your first line covers cases 2 and 4

your second line covers cases 4 and 5

you've missed case 3

in addition, the OR in the JOIN clause is problematic, because it sometimes results in very poor performance

so here's how i do it --

Code:

... WHERE pend >= fstart /* eliminates case 1 */
AND pstart <= fend /* eliminates case 6 */

notice that it's an AND which means both of those have to be true

okay, so that gets us cases 2, 3, 4, and 5, which each have some portion of overlap

now to calculate the amount of the overlap

the lower end of the overlap is given by GREATEST(fstart,pstart), and the upper end of the overlap is given by LEAST(fend,pend)

inspect the timeline diagram to assure yourself that this works in all cases 2 through 5

so the final query is
Code:

SELECT f.id
, f.machineId
, f.dateStart
, f.dateEnd
, p.id
, p.prodStart
, p.prodEnd
, GREATEST(f.dateStart,p.prodStart) AS start_overlap
, LEAST(f.dateEnd,p.prodEnd) AS end_overlap
, SEC_TO_TIME(
TIME_TO_SEC(LEAST(f.dateEnd,p.prodEnd) -
TIME_TO_SEC(GREATEST(f.dateStart,p.prodStart)
) AS overlap_duration
FROM fault AS f
INNER
JOIN production_calendar AS p
ON p.prodEnd >= f.dateStart /* eliminates case 1 */
AND p.prodStart <= f.dateEnd /* eliminates case 6 */

agrees : Thanks for picking up the slack! :)