Ask the Expert

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?

    Requires Free Membership to View

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


This was first published in April 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: