Q
Problem solve Get help with specific problems with your technologies, process and projects.

Consecutive dates within a range

How do I check if an employee has worked seven consecutive dates within a range?

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).

This was last published in November 2006

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