SQL实现指定日期范围按月统计员工任务耗时（分钟）

需求规则 #

• 统计维度：员工、自然月，统计单位为分钟
• 筛选逻辑：传入起止日期参数，返回筛选区间内每位员工各自然月的任务累计耗时
• 特殊规则：跨自然月的任务需拆分耗时，分别计入任务覆盖的对应月份统计

示例场景：筛选区间为1-Jan-2021至1-Jan-2022，员工X的一条任务起止时间为Task_start_date='2021-09-02 10:00:00.000'、Task_end_date='2021-12-02 14:00:00.000'，拆分后预期结果如下：

Employee	SEP-2021	OCT-2021	NOV-2021	DEC-2021
X	39720min	43200min	41160min	1700min

SQL实现（SQL Server 语法） #

实现逻辑：

• 通过递归CTE生成筛选区间覆盖的所有自然月时间序列
• 关联任务表，计算每条任务与对应重叠月份的有效时间差，得到单月耗时
• 最终通过行转列输出每个员工各月份的耗时结果

-- 定义筛选日期参数
DECLARE @fromdate DATE = '2021-01-01'
DECLARE @todate DATE = '2022-01-01'

;WITH MonthSeries AS (
    -- 生成筛选范围内所有自然月的起止时间
    SELECT 
        DATEFROMPARTS(YEAR(@fromdate), MONTH(@fromdate), 1) AS MonthStart,
        DATEADD(DAY, 1, EOMONTH(DATEFROMPARTS(YEAR(@fromdate), MONTH(@fromdate), 1))) AS MonthEnd
    UNION ALL
    SELECT 
        DATEADD(MONTH, 1, MonthStart),
        DATEADD(DAY, 1, EOMONTH(DATEADD(MONTH, 1, MonthStart)))
    FROM MonthSeries
    WHERE DATEADD(MONTH, 1, MonthStart) < @todate
),
TaskSplitCost AS (
    -- 拆分跨月任务，计算单月耗时分钟数
    SELECT
        t.Employee,
        CONCAT(DATENAME(MONTH, ms.MonthStart), '-', YEAR(ms.MonthStart)) AS MonthTag,
        DATEDIFF(MINUTE,
            CASE WHEN t.Task_start_date > ms.MonthStart THEN t.Task_start_date ELSE ms.MonthStart END,
            CASE WHEN t.Task_end_date < ms.MonthEnd THEN t.Task_end_date ELSE ms.MonthEnd END
        ) AS CostMinute
    FROM task_info t -- 替换为实际任务表名
    INNER JOIN MonthSeries ms
        ON t.Task_start_date < ms.MonthEnd
        AND t.Task_end_date >= ms.MonthStart
    WHERE t.Task_start_date < @todate
        AND t.Task_end_date >= @fromdate
)
-- 行转列输出格式化结果
SELECT 
    Employee,
    [January-2021] AS [JAN-2021], [February-2021] AS [FEB-2021], [March-2021] AS [MAR-2021],
    [April-2021] AS [APR-2021], [May-2021] AS [MAY-2021], [June-2021] AS [JUN-2021],
    [July-2021] AS [JUL-2021], [August-2021] AS [AUG-2021], [September-2021] AS [SEP-2021],
    [October-2021] AS [OCT-2021], [November-2021] AS [NOV-2021], [December-2021] AS [DEC-2021]
FROM (
    SELECT Employee, MonthTag, CONCAT(CostMinute, 'min') AS CostValue
    FROM TaskSplitCost
) src
PIVOT(
    MAX(CostValue)
    FOR MonthTag IN (
        [January-2021], [February-2021], [March-2021], [April-2021], [May-2021], [June-2021],
        [July-2021], [August-2021], [September-2021], [October-2021], [November-2021], [December-2021]
    )
) pvt

使用说明 #

• 将代码中task_info替换为实际存储任务数据的表名，确保表中存在Employee（员工标识字段）、Task_start_date（任务开始时间，datetime类型）、Task_end_date（任务结束时间，datetime类型）三个字段
• 若调整筛选日期区间，同步修改PIVOT子句中的月份列表即可适配不同时间范围的统计需求

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