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 last published in April 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close