Merge Multiple date ranges in SQL

WITH ftable as  (
SELECT * FROM faults
WHERE 
stop > CAST('2022-04-11' AS DATETIME) AND start < CAST('2022-05-17' AS DATETIME)
)
WITH ftable as  (
SELECT start, IFNULL(stop,NOW()) as stop FROM faults
WHERE 
(stop > CAST('2022-04-11' AS DATETIME) 
-- If you need with not-ending uncomment this 2 lines
-- OR stop is NULL
) AND start < CAST('2022-05-17' AS DATETIME)
)

SELECT  GREATEST(s1.start, CAST('2022-04-11' AS DATETIME)) as start,
   LEAST(min(t1.stop),CAST('2022-05-17' AS DATETIME)) as stop
FROM ftable s1 
INNER JOIN ftable t1 ON s1.start<= IFNULL(t1.stop, NOW())
  AND NOT EXISTS(SELECT * FROM ftable t2 
                 WHERE IFNULL(t1.stop, NOW()) >= t2.start AND IFNULL(t1.stop, NOW()) < IFNULL(t2.stop, NOW())) 
WHERE NOT EXISTS(SELECT * FROM ftable s2 
                 WHERE s1.start> s2.start AND s1.start<= IFNULL(s2.stop, NOW()))
GROUP BY s1.start
ORDER BY s1.start 

Vélemény, hozzászólás?

Az e-mail címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöltük