I have to write a query to select the employees who have not been absent for more than two consecutive days in...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.