本文介绍了在 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更多推荐
在 SQL 中计算连续缺席
发布评论