How do I check if an employee has worked seven consecutive dates within a range? For example, suppose the range is 10/09 though 10/15 and the employee table contains:
10/09 10/10 10/12 10/13 10/14 10/15
In the above example how do I tell the employee has not worked seven consecutive days?
If your range always covers exactly seven days, then just count the rows and make sure there are seven:
select employee_id from employees where date_worked between '2006-10-09' and '2006-10-15' group by employee_id having count(*) < 7
Note that the range covers exactly seven days, and that the HAVING clause will ensure the query returns only those employees with fewer than seven rows in this range.
If your range covers some variable number of days, and you wish to find employees who did not work on all of those days, then the HAVING clause can be modified to check the count of rows to be equal to the number of days in the range:
select employee_id from employees where date_worked between '2006-11-23' and '2006-12-05' group by employee_id having count(*) <= diffdays('2006-11-23','2006-12-05')
Here "DIFFDAYS()" is not a real function, but rather, a pseudo-function, which you must replace with the appropriate function or expression for your particular database system. The purpose of the function is to produce the number of days of difference between the two dates. Every database system has some way of producing this difference (some have more than one).
Whoopsies to watch out for are the sequence of specifying the two dates to avoid getting a negative number, and the fact that the difference between two consecutive days is 1, not 2 (and thus the HAVING clause must now use a less-than-or-equal operator).
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading