SQL Query返回给定数据范围存在的元组集

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.

我有一张表说EmployeeAbsence有三列:EmployeeId,LeaveReason和Date。该表记录了员工每天的出勤情况。我想知道过去14天休假的员工名单。

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.

在这种情况下,我的查询输出应该返回XYZ,因为它包含连续14天缺席的员工的条目。我很感激ORACLE查询。

2 个解决方案

#1

Your query will need two constraints

您的查询将需要两个约束

Data of leave must be less than 14 days away from current date

请假数据必须少于当前日期的14天

Employee has to appear only once if they have been on leave for several days / times during the 14 day period

如果员工在14天期间休假几天,则必须只出现一次

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

对于约束(1),您需要知道从日期B减去日期A会导致这两个日期之间的天数。

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

对于约束(2),您需要按员工ID进行分组。

That said

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

should do the trick.

应该做的伎俩。

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

我假设该表每天缺席1条记录,并且您不希望检索缺席的员工。上个月但在过去的14天里回来了。

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;

#1