0

I'm facing a strange issue with MySQL 5.5/5.6. I've implemented a classic merge intervals algorithm in SQL, but it's not working as expected with some rows. I used Merging intervals in one pass in SQL and Most efficient way to sum overlapping datetimes in MySQL as starting references (C++ @ http://www.geeksforgeeks.org/merging-intervals/)

Anyone able to spot any issue on this? 

SELECT MIN(inizio) as inizio, MAX(fine) as fine 
FROM (
    SELECT 
    @interval_id := IF(attendances.inizio > @interval_end, @interval_id + 1, @interval_id) AS interval_id, 
    @interval_end := IF(attendances.inizio < @interval_end, GREATEST(@interval_end, attendances.fine), attendances.fine) AS interval_end, 
    attendances.inizio, 
    attendances.fine 
    FROM attendances 
    INNER JOIN attendance_sheets ON ( attendance_sheet_id = attendance_sheets.id) 
    ORDER BY attendances.inizio,attendances.fine 
) intervals GROUP BY interval_id;

From these rows

enter image description here

I get a skipped @interval_id and wrong @interval_end for

enter image description here

which results in a wrong merge for 2017-01-26

enter image description here

thanks in advance nicola

Nicola Beghin
  • 464
  • 3
  • 17
  • Probably need >= in your start date expression. Or <= in your end date. – Jacob H May 22 '17 at 19:20
  • Unfortunately I checked that as the first step, it's not related to <=/>=. What's more absurd, removing the 2017-01-28 10:00-12:30 everything works again. I've no words.. – Nicola Beghin May 23 '17 at 19:45

0 Answers0