Consecutive Dates

G

GMD

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

John Spencer

Is that four consecutive days or four consecutive work days? The latter is
more difficult; especially if you throw in holidays.
 
J

John Spencer

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