Consecutive Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have downloaded employee information from a corporate mainframe into a
table named EXCEPTIONS. There are two fields named Employee and Date. The
Date field has dates employees have been absent from work. I want to be able
to identify those employees and the dates they were absent for four
consecutive days or greater. Any help would be appreciated.
 
Is that four consecutive days or four consecutive work days? The latter is
more difficult; especially if you throw in holidays.
 
SELECT Employee, Date
FROM Table as A
WHERE
(SELECT COUNT(*)
FROM Table As B
WHERE B.Date >=A.Date
AND B.Date <= DateAdd("d",3,A.Date)
And B.Employee = A.Employee) > 3

That obviously doesn't work for consecutive work days unless it happens to
be M to Th or T to F.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need a consecutive date total Report from one date field in table. 3
Subquery can only return one record when used inside the WHERE cla 2
Calculating certain Days of the week 7
Two or more consecutive days 5
date calculation 5
Update Query based on Difference in time 3
Awkward query with dates! 1
update query 4

Back
Top