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.