MySQL Implementation of Consecutive Date Ranges -

i have 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 `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 1 or multiple contract lines per employee

select * 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 query contracts table group consecutive ranges per employee? i'm looking 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 employee 666 return lowest start date , highest end date taking account there no gap between contract dates.

record employee 777 return 2 lines since there gap between record id 5 , 6.

any ideas?

the logic isn't hard, implementation in mysql is. idea add flag indicates beginning of contract start. then, each row, cumulative sum of this. cumulative sum can used grouping purposes.

the first step can use correlated subquery:

   select c1.*,         (not exists (select 1                     contracts c2                     c1.employee_id = c2.employee_id ,                           c1.start_date = c2.end_date + interval 1 day                    )        ) startflag contracts c1; 

the second uses subquery , cumulative sum:

    select  c0.* ,(@rn := @rn + coalesce(startflag, 0)) cumestarts  (select c1.*,              (not exists (select 1                           contracts c2                           c1.employee_id = c2.employee_id ,                                 c1.start_date = c2.end_date + interval 1 day                          )              ) startflag       contracts c1       order employee_id, start_date  ) c0 cross join (select @rn := 0) params; 

the final step aggregate value:

select  c.employee_id ,min(c.start_date) start_date ,max(c.end_date) end_date ,count(*) numcontracts  (         select          c0.*         ,(@rn := @rn + coalesce(startflag, 0)) cumestarts                  (select c1.*,                  (not exists (select 1                       contracts c2                       c1.employee_id = c2.employee_id ,                         c1.start_date = c2.end_date + interval 1 day                      )                  ) startflag               contracts c1               order employee_id, start_date          ) c0 cross join (select @rn := 0) params  ) c group c.employee_id, c.cumestarts