My SQL Finding a span of dates accross rows

mysql

Solution

The theory is similar to @mellamokb's answer, but somewhat more concise:

SELECT employee, MIN(start) start, end
FROM (
  SELECT   @end:=IF(employee<=>@emp AND @stt<=end+INTERVAL 21 DAY,@end,end) end,
           @stt:=start start,
           @emp:=employee AS employee
  FROM     my_table, (SELECT @emp:=NULL, @stt:=0, @end:=0) init
  ORDER BY employee, start DESC
) t
GROUP BY employee, end

See it on sqlfiddle.

Problem

I am looking for some help with even knowing where to start. Essentially we have a table for clients that hold employment start dates and end dates. For annual reports we have to calculate "continuous employment" which is defined as earliest start date to last end date as long as there is not more than 21 days between one end date and the next start date. here is an example ``` employee | Start Date | End Date 1 | 2012-10-1 | 2012-11-05 1 | 2012-11-08 | 2013-1-25 2 | 2012-10-1 | 2012-11-05 2 | 2012-11-30 | 2013-1-02 ``` in the above, i would like to see employee 1 as continuously employed from 2012-10-1 to 2013-1-25 but employee 2 would have 2 separate employment lines showing continuous employment from 2012-10-1 to 2012-11-05 and a different from 012-11-30 to 2013-1-02 Thanks for the help!

Original source