Source : https://bertwagner.com/posts/gaps-and-islands/

Alors voici le problème : Vous voulez faire un calendrier sur votre site WEB, avec des événements qui peuvent se recouper.

Créons une table de test :

DROP TABLE IF EXISTS OverlappingDateRanges;

CREATE TABLE OverlappingDateRanges (StartDate date, EndDate date); 

Insérons quelques données

INSERT INTO OverlappingDateRanges VALUES
('2017-08-24', '2017-09-23'),
('2017-08-24', '2017-09-20'),
('2017-09-23', '2017-09-27'),
('2017-09-25', '2017-10-10'),
('2017-10-17', '2017-10-18'),
('2017-10-25', '2017-11-03'),
('2017-11-03', '2017-11-15')

Vérification du contenu :

SELECT * FROM mes_evenements

StartDate	EndDate
2017-08-24	2017-09-23
2017-08-24	2017-09-20
2017-09-23	2017-09-27
2017-09-25	2017-10-10
2017-10-17	2017-10-18
2017-10-25	2017-11-03
2017-11-03	2017-11-15

Voici la requête qui va constituer des groupes avec les plages de date qui se recoupent :

SELECT
MIN(StartDate) AS IslandStartDate,
MAX(EndDate) AS IslandEndDate
FROM
(
SELECT
*,
CASE WHEN Groups.PreviousEndDate >= StartDate THEN 0 ELSE 1 END AS IslandStartInd,
SUM(CASE WHEN Groups.PreviousEndDate >= StartDate THEN 0 ELSE 1 END) OVER (ORDER BY Groups.RN) AS IslandId
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY StartDate,EndDate) AS RN,
StartDate,
EndDate,
LAG(EndDate,1) OVER (ORDER BY StartDate, EndDate) AS PreviousEndDate
FROM
OverlappingDateRanges
) Groups
) Islands
GROUP BY
IslandId
ORDER BY
IslandStartDate

Résultat :

IslandStartDate	IslandEndDate
2017-08-24	2017-10-10
2017-10-17	2017-10-18
2017-10-25	2017-11-15