基于SQL内连接的学员考勤报表开发：含状态计算与备注判定

需求拆解 #

先把你的需求再明确下，确保没遗漏：

• 单日出勤验证：根据学员的Schedule规则（如SAT-MON-WED对应每周3天），计算当日InTime到OutTime的出勤时长，达标标记为YES，未达标为NO
• 周出勤状态：统计本周（周六至周四）的出勤时长占该周要求总时长的百分比
• 自定义区间总状态：支持传入指定日期范围，计算该区间内总出勤时长占要求总时长的百分比
• 特殊状态标记：1个月未出勤的学员标记为Discontinued；近6天内出勤天数≤3天的标记为NO

完整实现SQL #

假设两张表的关联字段为StudentID，学员表tsStudenList包含StudentID、Schedule（如SAT-MON-WED）、Duration（每日要求出勤时长，单位小时）字段，考勤表tsStudAttandance包含StudentID、AttendanceDate、InTime、OutTime字段。你可以根据实际字段名调整：

-- 定义参数：自定义日期区间（可按需修改，默认最近30天）
DECLARE @StartDate DATE = DATEADD(DAY, -30, GETDATE());
DECLARE @EndDate DATE = GETDATE();

WITH ScheduleDays AS (
    -- 解析学员的Schedule，转换为星期数字（1=周一，6=周六，7=周日）
    SELECT 
        sl.StudentID,
        CASE 
            WHEN value = 'MON' THEN 1
            WHEN value = 'TUE' THEN 2
            WHEN value = 'WED' THEN 3
            WHEN value = 'THU' THEN 4
            WHEN value = 'FRI' THEN 5
            WHEN value = 'SAT' THEN 6
            WHEN value = 'SUN' THEN 7
        END AS WeekDayNum
    FROM [dbo].[tsStudenList] sl
    CROSS APPLY STRING_SPLIT(sl.Schedule, '-')
),
WeeklyRequiredDays AS (
    -- 计算每个学员每周应出勤天数
    SELECT StudentID, COUNT(*) AS WeeklyDays
    FROM ScheduleDays
    GROUP BY StudentID
),
PeriodRequiredDays AS (
    -- 计算自定义日期区间内，每个学员的要求出勤天数
    SELECT 
        sl.StudentID,
        COUNT(*) AS PeriodDays
    FROM [dbo].[tsStudenList] sl
    CROSS JOIN (
        -- 生成日期区间内的所有日期
        SELECT DATEADD(DAY, number, @StartDate) AS DateInPeriod
        FROM master..spt_values 
        WHERE type = 'P' AND DATEADD(DAY, number, @StartDate) <= @EndDate
    ) dates
    INNER JOIN ScheduleDays sd ON sl.StudentID = sd.StudentID
        AND DATEPART(WEEKDAY, dates.DateInPeriod) = sd.WeekDayNum
    GROUP BY sl.StudentID
),
DailyAttendance AS (
    -- 计算单日出勤时长与达标状态
    SELECT 
        sa.StudentID,
        sa.AttendanceDate,
        -- 计算出勤时长（小时，保留两位小数）
        ROUND(ISNULL(DATEDIFF(MINUTE, sa.InTime, sa.OutTime)/60.0, 0), 2) AS DailyHours,
        -- 判断单日是否达标
        CASE 
            WHEN ISNULL(DATEDIFF(MINUTE, sa.InTime, sa.OutTime)/60.0, 0) >= sl.Duration THEN 'YES'
            ELSE 'NO'
        END AS DailyStatus
    FROM [dbo].[tsStudAttandance] sa
    INNER JOIN [dbo].[tsStudenList] sl ON sa.StudentID = sl.StudentID
)
-- 最终报表
SELECT 
    sl.StudentID,
    sl.StudentName, -- 假设学员表有姓名字段，可替换为实际字段名
    -- 单日出勤信息（取最新单日数据，如需全量单日记录可调整逻辑）
    MAX(da.DailyHours) AS LatestDailyHours,
    MAX(da.DailyStatus) AS LatestDailyStatus,
    -- 本周（周六至周四）出勤统计
    ROUND(
        ISNULL(SUM(CASE WHEN da.AttendanceDate BETWEEN DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5) AND DATEADD(DAY, 4, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5)) THEN da.DailyHours ELSE 0 END), 0) 
        / (sl.Duration * wrd.WeeklyDays) * 100,
        2
    ) AS WeeklyAttendanceRate,
    CASE 
        WHEN ROUND(ISNULL(SUM(CASE WHEN da.AttendanceDate BETWEEN DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5) AND DATEADD(DAY, 4, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5)) THEN da.DailyHours ELSE 0 END), 0) / (sl.Duration * wrd.WeeklyDays) * 100, 2) >= 80 THEN 'YES'
        ELSE 'NO'
    END AS WeeklyStatus,
    -- 自定义区间总出勤统计
    ROUND(
        ISNULL(SUM(CASE WHEN da.AttendanceDate BETWEEN @StartDate AND @EndDate THEN da.DailyHours ELSE 0 END), 0) 
        / (sl.Duration * prd.PeriodDays) * 100,
        2
    ) AS TotalAttendanceRate,
    CASE 
        WHEN ROUND(ISNULL(SUM(CASE WHEN da.AttendanceDate BETWEEN @StartDate AND @EndDate THEN da.DailyHours ELSE 0 END), 0) / (sl.Duration * prd.PeriodDays) * 100, 2) >= 80 THEN 'YES'
        ELSE 'NO'
    END AS TotalStatus,
    -- 特殊状态标记
    CASE WHEN MAX(da.AttendanceDate) < DATEADD(MONTH, -1, GETDATE()) THEN 'Discontinued' ELSE '' END AS SpecialStatus,
    CASE WHEN (SELECT COUNT(DISTINCT AttendanceDate) FROM [dbo].[tsStudAttandance] WHERE StudentID = sl.StudentID AND AttendanceDate >= DATEADD(DAY, -6, GETDATE())) <= 3 THEN 'NO' ELSE '' END AS RecentAttendanceStatus
FROM [dbo].[tsStudenList] sl
LEFT JOIN DailyAttendance da ON sl.StudentID = da.StudentID
LEFT JOIN WeeklyRequiredDays wrd ON sl.StudentID = wrd.StudentID
LEFT JOIN PeriodRequiredDays prd ON sl.StudentID = prd.StudentID
GROUP BY sl.StudentID, sl.StudentName, sl.Duration, wrd.WeeklyDays, prd.PeriodDays
ORDER BY sl.StudentID;

关键逻辑解释 #

1. Schedule解析：用STRING_SPLIT拆分Schedule字符串，把星期缩写转换成对应的星期数字，方便后续计算每周/区间内的要求出勤天数
2. 单日出勤计算：用DATEDIFF计算InTime到OutTime的分钟数，转换为小时数，和Duration对比判断是否达标
3. 本周范围计算：DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 5)获取本周六的日期，再加4天得到本周四，完全匹配你要求的周六至周四的周范围
4. 特殊状态判断：
   • Discontinued：判断学员最后一次出勤日期是否早于1个月前
   • 近6天出勤不足：统计近6天内的出勤天数，≤3天则标记为NO
5. 避免报错：用ISNULL处理出勤时长为0或NULL的情况，防止百分比计算出现除以0的错误

注意事项 #

• 如果你的表字段名（比如学员姓名、关联字段）和假设不同，记得替换成实际字段
• 周状态和总状态的达标阈值（比如示例中的80%）可以根据你的实际需求调整
• 如果需要包含从未有过考勤记录的学员，保持LEFT JOIN即可；如果只需要有考勤记录的学员，改成INNER JOIN

内容的提问来源于stack exchange，提问作者Fussionweb