在 SQL 中计算连续缺席

本文介绍了在 SQL 中计算连续缺席的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要在 SQL 中计算日期范围内连续缺勤次数为 X 次的所有员工.

I need to calculate all Employees that have X number of consecutive absences within a date range in SQL.

我们有一个缺勤表，其中包含员工缺勤的每一天的 1 条记录，还有一个包含全年工作日的日历表.

We have an Absences Table with 1 record for each day an employee is absent and a Calendar Table with the work days for the year.

tblAbsences EmployeeID int AbsenceDate datetime tblCalendar WorkDay datetime

有人知道如何计算连续缺勤吗?示例:在 2009 年 1 月 1 日至 2009 年 3 月 1 日之间连续 3 次缺勤的所有员工.

Does anyone have any ideas how to calculate consecutive absences? Example: All employees that have 3 consecutive absences between 1/1/2009 and 3/1/2009.

推荐答案

这应该适合你.在 ConsecDates 上使用 GROUP BY 查找缺席次数超过 X 次的人.

This should work for you. GROUP BY on ConsecDates to find who was absent more than X number of times.

select a.*, ( select min(b.absenceDate) from tblAbsences b where a.employeeId = b.employeeId and b.absenceDate >= a.absenceDate and not exists ( select 1 from tblabsences c where c.employeeId = b.employeeId and dateadd( dd, 1, b.absenceDate) = c.absenceDate ) ) ConsecDates from dbo.tblAbsences a order by a.AbsenceDate asc