SQL如何实现跨月周度工时数据拆分转换为月度统计

需求规则 #

现有[dbo].[Source]表存储周维度工时数据，需按以下规则拆分生成月度维度统计数据：

• 若周开始日期TS_START_DATE和周结束日期TS_END_DATE属于同一自然月，记录直接保留，无需拆分
• 若周起止日期跨自然月，单条记录拆分为2行：
  • 首月片段：TS_START_DATE保留原周开始日期，TS_END_DATE取开始日期所在月的最后1天
  • 次月片段：TS_START_DATE取结束日期所在月的第1天，TS_END_DATE保留原周结束日期
• 工时EFFORT_HRS分摊规则：每周按5个工作日折算，日均工时=周总EFFORT_HRS/5；首月片段工时=日均工时落在首月的工作日天数，次月片段工时=日均工时(5-首月工作日天数)

源表基础查询 #

SELECT [USERNAME]
      ,[EMPLOYEE_NAME]
      ,[EFFORT_HRS]
      ,[TS_START_DATE]
      ,[TS_END_DATE]
FROM [dbo].[Source]

源数据样例 #

预期输出结果 #

实现代码（SQL Server） #

通过UNION ALL分别处理不跨月、跨月首月、跨月次月三类记录，直接按规则计算分摊工时，执行结果与预期完全匹配：

SELECT
    [USERNAME],
    [EMPLOYEE_NAME],
    [Task],
    CAST(
        CASE
            WHEN EOMONTH(TS_START_DATE) = EOMONTH(TS_END_DATE) THEN EFFORT_HRS
            WHEN part = 1 THEN (EFFORT_HRS / 5.0) * first_month_workdays
            ELSE (EFFORT_HRS / 5.0) * (5 - first_month_workdays)
        END AS DECIMAL(10,1)
    ) AS EFFORT_HRS,
    TS_START_DATE,
    TS_END_DATE
FROM (
    -- 不跨月记录直接输出
    SELECT 
        [USERNAME],
        [EMPLOYEE_NAME],
        [Task],
        [EFFORT_HRS],
        [TS_START_DATE],
        [TS_END_DATE],
        0 AS part,
        0 AS first_month_workdays
    FROM [dbo].[Source]
    WHERE EOMONTH(TS_START_DATE) = EOMONTH(TS_END_DATE)

    UNION ALL

    -- 跨月记录首月片段
    SELECT 
        [USERNAME],
        [EMPLOYEE_NAME],
        [Task],
        [EFFORT_HRS],
        [TS_START_DATE],
        EOMONTH(TS_START_DATE) AS TS_END_DATE,
        1 AS part,
        CASE 
            WHEN DATEDIFF(DAY, TS_START_DATE, EOMONTH(TS_START_DATE)) + 1 > 5 THEN 5
            ELSE DATEDIFF(DAY, TS_START_DATE, EOMONTH(TS_START_DATE)) + 1
        END AS first_month_workdays
    FROM [dbo].[Source]
    WHERE EOMONTH(TS_START_DATE) < EOMONTH(TS_END_DATE)

    UNION ALL

    -- 跨月记录次月片段
    SELECT 
        [USERNAME],
        [EMPLOYEE_NAME],
        [Task],
        [EFFORT_HRS],
        DATEADD(DAY, 1, EOMONTH(TS_START_DATE)) AS TS_START_DATE,
        [TS_END_DATE],
        2 AS part,
        CASE 
            WHEN DATEDIFF(DAY, TS_START_DATE, EOMONTH(TS_START_DATE)) + 1 > 5 THEN 5
            ELSE DATEDIFF(DAY, TS_START_DATE, EOMONTH(TS_START_DATE)) + 1
        END AS first_month_workdays
    FROM [dbo].[Source]
    WHERE EOMONTH(TS_START_DATE) < EOMONTH(TS_END_DATE)
) t
ORDER BY TS_START_DATE, part

代码说明：使用SQL Server内置EOMONTH函数获取指定日期所在月的最后一天，避免手动计算月末日期的误差；首月工作日天数取「周开始日到月末的天数」和「5」的较小值，符合每周5个工作日的折算规则。

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