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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top