在 SQL 中计算连续缺席 [英] Calculating Consecutive Absences in 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 中计算连续缺席的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！