如何基于Timesheet与Leaves表生成指定周期的DailyWorkReport？

报表周期：11/1/2022 至 11/5/2022

原始数据表 #

Timesheet 表 #

Leaves 表 #

目标输出报表 DailyWorkReport #

实现步骤 #

1. 数据预处理 #

• 拆分Timesheet表的user_id字段，从jon 101这类格式中提取用户名和用户ID；修正Login_by中的异常时间（比如12:20:100 AM属于输入错误，需确认正确值或按有效打卡记录处理）。
• 对Leaves表去重，同一用户同一日期的重复请假记录只保留一条，避免统计偏差。

2. 构建日期维度 #

列出报表周期内的所有日期（11/1/2022至11/5/2022），作为后续按日期统计的基础。

3. 关联工时与请假状态 #

• 从Timesheet中按「用户+日期」分组，默认当日有打卡记录则计为8工时（与目标输出规则一致）。
• 从Leaves中按「用户+日期」标记请假状态，当日有请假记录则标记为Leave。

4. 行列转换生成报表 #

将日期维度从行转为列，按用户分组后填充每日状态：

• 当日有打卡且无请假，填充8；
• 当日无打卡或有请假，填充Leave；
• 注：目标中jon仅11/1有打卡记录，后续日期按请假处理；tom在11/1-11/3有打卡，后续日期显示Leave。

示例SQL实现（MySQL） #

-- 1. 生成日期维度
WITH date_dim AS (
    SELECT '2022-11-01' AS report_date UNION ALL
    SELECT '2022-11-02' UNION ALL
    SELECT '2022-11-03' UNION ALL
    SELECT '2022-11-04' UNION ALL
    SELECT '2022-11-05'
),
-- 2. 清洗Timesheet数据，拆分用户信息
clean_timesheet AS (
    SELECT 
        SUBSTRING_INDEX(user_id, ' ', 1) AS user_name,
        DATE_FORMAT(start_time_server, '%m/%d/%Y') AS work_date,
        8 AS work_hours
    FROM Timesheet
),
-- 3. 去重Leaves数据
clean_leaves AS (
    SELECT DISTINCT
        user_name,
        DATE_FORMAT(Leave_applied_date, '%m/%d/%Y') AS leave_date
    FROM Leaves
),
-- 4. 关联用户、日期、工时和请假数据
user_daily_data AS (
    SELECT 
        dd.report_date,
        COALESCE(ts.user_name, lv.user_name) AS user_name,
        CASE 
            WHEN ts.work_hours IS NOT NULL AND lv.leave_date IS NULL THEN ts.work_hours
            ELSE 'Leave'
        END AS status
    FROM date_dim dd
    LEFT JOIN clean_timesheet ts ON dd.report_date = ts.work_date
    LEFT JOIN clean_leaves lv ON dd.report_date = lv.leave_date AND ts.user_name = lv.user_name
    UNION
    SELECT 
        dd.report_date,
        lv.user_name,
        'Leave' AS status
    FROM date_dim dd
    JOIN clean_leaves lv ON dd.report_date >= lv.leave_date
    WHERE lv.user_name NOT IN (SELECT DISTINCT user_name FROM clean_timesheet WHERE work_date = dd.report_date)
)
-- 5. 行列转换生成目标报表
SELECT 
    user_name,
    MAX(CASE WHEN report_date = '2022-11-01' THEN status END) AS '11/1/2022',
    MAX(CASE WHEN report_date = '2022-11-02' THEN status END) AS '11/2/2022',
    MAX(CASE WHEN report_date = '2022-11-03' THEN status END) AS '11/3/2022',
    MAX(CASE WHEN report_date = '2022-11-04' THEN status END) AS '11/4/2022',
    MAX(CASE WHEN report_date = '2022-11-05' THEN status END) AS '11/5/2022'
FROM user_daily_data
GROUP BY user_name;

注意事项 #

• Leaves表存在数据一致性问题（比如user_id=102但user_name=jon），需先修正该问题，否则会影响报表准确性。
• 若使用Excel、Python Pandas等工具，思路一致：先清洗数据，生成日期维度，关联工时与请假信息，最后通过透视表完成行列转换。

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