Rows with consecutive dates

I have to write a query to select the employees who have not been absent for more than two consecutive days in a given year. The table contains employee number and the date the employee was absent on. How can I do this?

Your table probably looks something like this:

empabsences
empno dateabsent
  1   2003-01-01
  1   2003-02-28
  1   2003-03-01
  1   2003-03-02
  1   2003-03-03
  3   2003-03-01
  3   2003-03-02
  3   2003-03-03
  4   2003-03-04
  5   2003-01-05
  5   2003-01-06
  5   2003-01-08
  5   2003-01-09
  5   2003-01-10
  8   2003-01-08

Employees absent for any three consecutive days can be found as follows (the DATEADD function is Microsoft Access syntax, so you'll want to adjust this for whatever database you're using):

select empno, dateabsent
  from empabsences  X
 where exists 
       ( select 1 from empabsences
          where empno = X.empno
            and dateabsent 
              = dateadd("d",-1,X.dateabsent) )
   and exists 
       ( select 1 from empabsences
          where empno = X.empno
            and dateabsent 
              = dateadd("d",-2,X.dateabsent) )

The results of this query for the given sample data are:

empno dateabsent
  1   2003-03-02
  1   2003-03-03
  3   2003-03-03
  5   2003-01-10

This identifies all occurrences of the third consecutive day absent. Notice that employee 1 was absent 4 consecutive days, and that both the 3rd and 4th are reported.

The problem was to report employees who weren't absent any three consecutive days, so we need a query on the main employee table. Remove dateabsent from the above query, add DISTINCT so that it returns unique empno values, and place it into a NOT IN subselect within an outer query on the employee table:

select empno
  from employees 
 where empno not in
       ( select distinct empno
           from empabsences  X
          where exists 
                ( select 1 from empabsences
                   where empno = X.empno
                     and dateabsent 
                       = dateadd("d",-1,X.dateabsent) )
            and exists 
                ( select 1 from empabsences
                   where empno = X.empno
                     and dateabsent 
                       = dateadd("d",-2,X.dateabsent) )
       )

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.