Q

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


This was first published in April 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close