SQL Query which returns set of tuples that exists for a given data range

Question

I have a table say EmployeeAbsence that has three columns: EmployeeId, LeaveReason and Date. This table records attendance of employees on a daily basis. I want to know the list of employees who took leave in last 14 days.

For example,

1 XYZ 2009-07-20
2 XYZ 2009-07-19
--
---
--
1001 XYZ 2009-07-04

In this case, my query output should return XYZ and alike because it contains entries for employees who were absent for last 14 days consecutively. I would appreciate an ORACLE query.

Answer 1

Your query will need two constraints

1. Data of leave must be less than 14 days away from current date
2. Employee has to appear only once if they have been on leave for several days / times during the 14 day period

For constraint (1) you need to know that subtracting date A from date B results in the number of days between those two dates.

For constraint (2) you need to group by the employees ID.

That said

SELECT EmployeeID
FROM EmployeeAbsence
WHERE Date between SYSDATE - 14 and SYSDATE
GROUP BY EmployeeId

should do the trick.

Answer 2

I assume that table has 1 record for each day of absence and you don't want to retrieve employees that were absent for e.g. last month but returned during last 14 days.

SELECT employeeId
FROM employeeAbsences base
WHERE date > trunc(sysdate)-15 -- we want to include one more day for HAVING clause to work with 
GROUP BY employeeId
-- had 2 or more non-consecutive leaves --> at least one of them started during last 14 days
HAVING count(*) < max(date) - min(date) 
   -- first absence is less than 14 days ago
   OR min(date) > trunc(sysdate) - 14;