请求创建考勤数据视图：基于TBLLOGFULL表生成指定字段视图

基于TBLLOGFULL表（包含IDUser和TimeCheck字段），创建包含IDUser、WorkingDate、WorkingShift、Timecheckin、Timecheckout字段的视图，实现规则如下：

规则说明 #

• 按IDUser、WorkingDate分组统计
• 班次判定逻辑：
  • S1：当日最早打卡时间在5:00-7:00区间，最晚打卡时间在13:50-15:00区间
  • S2：当日最早打卡时间在13:00-15:00区间，最晚打卡时间在21:00-23:00区间
  • S3：当日20:00-23:00有打卡记录，次日5:00-7:00有打卡记录，且两次打卡时间差≤9小时，WorkingDate取当日日期
• 签到时间：S3班次取当日20-23点的最早打卡记录，其他班次取当日最早打卡记录
• 签退时间：S3班次取次日5-7点的最晚打卡记录，其他班次取当日最晚打卡记录

SQL 实现代码 #

CREATE VIEW VW_WORKING_SHIFT AS
WITH LOG_DATA AS (
    SELECT 
        IDUser,
        TimeCheck,
        CAST(TimeCheck AS DATE) AS WorkingDate,
        CAST(TimeCheck AS TIME) AS CheckTime
    FROM TBLLOGFULL
),
-- 处理当日完成打卡的S1、S2班次
S1_S2_DATA AS (
    SELECT 
        IDUser,
        WorkingDate,
        MIN(TimeCheck) AS MinCheck,
        MAX(TimeCheck) AS MaxCheck,
        CASE
            WHEN MIN(CheckTime) BETWEEN '05:00:00' AND '07:00:00' 
                 AND MAX(CheckTime) BETWEEN '13:50:00' AND '15:00:00' THEN 'S1'
            WHEN MIN(CheckTime) BETWEEN '13:00:00' AND '15:00:00' 
                 AND MAX(CheckTime) BETWEEN '21:00:00' AND '23:00:00' THEN 'S2'
            ELSE NULL
        END AS WorkingShift
    FROM LOG_DATA
    GROUP BY IDUser, WorkingDate
    HAVING CASE
            WHEN MIN(CheckTime) BETWEEN '05:00:00' AND '07:00:00' 
                 AND MAX(CheckTime) BETWEEN '13:50:00' AND '15:00:00' THEN 'S1'
            WHEN MIN(CheckTime) BETWEEN '13:00:00' AND '15:00:00' 
                 AND MAX(CheckTime) BETWEEN '21:00:00' AND '23:00:00' THEN 'S2'
            ELSE NULL
        END IS NOT NULL
),
-- 处理跨天打卡的S3班次
S3_DATA AS (
    SELECT 
        l1.IDUser,
        l1.WorkingDate,
        'S3' AS WorkingShift,
        MIN(l1.TimeCheck) AS Timecheckin,
        MAX(l2.TimeCheck) AS Timecheckout
    FROM LOG_DATA l1
    JOIN LOG_DATA l2 
        ON l1.IDUser = l2.IDUser
        AND l2.WorkingDate = DATEADD(DAY, 1, l1.WorkingDate)
    WHERE l1.CheckTime BETWEEN '20:00:00' AND '23:00:00'
        AND l2.CheckTime BETWEEN '05:00:00' AND '07:00:00'
        AND DATEDIFF(HOUR, l1.TimeCheck, l2.TimeCheck) <= 9
    GROUP BY l1.IDUser, l1.WorkingDate
)
-- 合并所有班次数据并格式化时间显示
SELECT 
    IDUser,
    WorkingDate,
    WorkingShift,
    FORMAT(Timecheckin, 'hh:mm:ss tt') AS Timecheckin,
    FORMAT(Timecheckout, 'hh:mm:ss tt') AS Timecheckout
FROM (
    SELECT 
        IDUser,
        WorkingDate,
        WorkingShift,
        MinCheck AS Timecheckin,
        MaxCheck AS Timecheckout
    FROM S1_S2_DATA
    UNION ALL
    SELECT 
        IDUser,
        WorkingDate,
        WorkingShift,
        Timecheckin,
        Timecheckout
    FROM S3_DATA
) AS CombinedData
ORDER BY IDUser, WorkingDate;

代码逻辑解析 #

1. LOG_DATA CTE：将TimeCheck拆分为日期（WorkingDate）和纯时间（CheckTime）字段，简化后续时间范围的判断操作。
2. S1_S2_DATA CTE：按用户和日期分组，通过当日最早、最晚打卡的时间区间判定S1/S2班次，过滤掉不符合条件的分组记录。
3. S3_DATA CTE：关联当日晚班打卡记录与次日早班打卡记录，验证时间差要求后生成S3班次数据。
4. 合并查询：将S1/S2、S3班次数据合并，格式化签到、签退时间为时分秒+AM/PM格式，最终输出符合预期的统计结果。

内容的提问来源于stack exchange，提问作者Pham Xuan Quynh