MySQL Implementation of Consecutive Date Ranges

Question

I have a MySQL database containing contracts table:

CREATE TABLE IF NOT EXISTS `contracts` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `employee_id` BIGINT(20) DEFAULT NULL,
    `start_date` DATE DEFAULT NULL,
    `end_date` DATE DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `contracts` (`id`,`employee_id`,`start_date`,`end_date`) 
VALUES 
(1, 555, '2010-01-01', '2012-12-31'), 
(2, 666, '2013-01-01', '2013-05-01'), 
(3, 666, '2013-05-02', '2013-10-11'),
(4, 777, '2012-01-10', '2013-03-01'),
(5, 777, '2013-03-02', '2014-07-15'),
(6, 777, '2015-01-16', '2015-05-20');

Querying it I get one or multiple contract lines per employee

SELECT * FROM contracts


id  employee_id     start_date  end_date
1   555             2010-01-01  2012-12-31
2   666             2013-01-01  2013-05-01
3   666             2013-05-02  2013-10-11
4   777             2012-01-10  2013-03-01
5   777             2013-03-02  2014-07-15
6   777             2015-01-16  2015-05-20

How do I query contracts table to group consecutive ranges per employee? I'm looking for this output:

employee_id     start_date  end_date
555             2010-01-01  2012-12-31
666             2013-01-01  2013-10-11
777             2012-01-10  2014-07-15
777             2015-01-16  2015-05-20

Record for employee 666 would return lowest start date and highest end date taking into account there is no gap between contract dates.

Record for employee 777 would return two lines since there is a gap between record id 5 and 6.

Any ideas?

Answer 1

The logic isn't so hard, but the implementation in MySQL is. The idea is to add a flag that indicates the beginning of a contract start. Then, for each row, do a cumulative sum of this. The cumulative sum can be used for grouping purposes.

The first step can use a correlated subquery:

   SELECT c1.*, 
       (NOT EXISTS (SELECT 1
                    FROM contracts c2
                    WHERE c1.employee_id = c2.employee_id AND
                          c1.start_date = c2.end_date + INTERVAL 1 DAY
                   )
       ) AS startflag
FROM contracts c1;

The second uses this as a subquery and does a cumulative sum:

    SELECT 
c0.*
,(@rn := @rn + COALESCE(startflag, 0)) AS cumestarts
FROM 
(SELECT c1.*,
             (NOT EXISTS (SELECT 1
                          FROM contracts c2
                          WHERE c1.employee_id = c2.employee_id AND
                                c1.start_date = c2.end_date + INTERVAL 1 DAY
                         )
             ) AS startflag
      FROM contracts c1
      ORDER BY employee_id, start_date

) c0 CROSS JOIN (SELECT @rn := 0) params;

The final step is to aggregate by this value:

SELECT 
c.employee_id
,MIN(c.start_date) AS start_date
,MAX(c.end_date) AS end_date
,COUNT(*) AS numcontracts
FROM 
(
        SELECT 
        c0.*
        ,(@rn := @rn + COALESCE(startflag, 0)) AS cumestarts
        FROM 
        (SELECT c1.*,
                 (NOT EXISTS (SELECT 1
                      FROM contracts c2
                      WHERE c1.employee_id = c2.employee_id AND
                        c1.start_date = c2.end_date + INTERVAL 1 DAY
                     )
                 ) AS startflag
              FROM contracts c1
              ORDER BY employee_id, start_date

        ) c0 CROSS JOIN (SELECT @rn := 0) params

) c
GROUP BY c.employee_id, c.cumestarts