SQL Server中任务时长按日期区间按月比例分配的实现

针对跨多个月份的任务，要按工作天数比例分配总时长到对应月份，核心是先枚举任务覆盖的所有月份，再分别计算每个月份内的有效工作天数占比，最后推导对应时长。下面提供两种高效方案：

方案一：递归CTE生成月份序列（临时场景适用） #

无需依赖额外表，用递归CTE生成任务StartDate到EndDate之间的所有月份，再计算每个月的工作天数占比：

WITH TaskMonths AS (
    -- 初始锚点：任务的起始月份
    SELECT 
        Id,
        StartDate,
        EndDate,
        Duration,
        DATEFROMPARTS(YEAR(StartDate), MONTH(StartDate), 1) AS MonthStart,
        EOMONTH(StartDate) AS MonthEnd
    FROM YourTaskTable
    UNION ALL
    -- 递归生成后续月份，直到超过任务结束月份
    SELECT 
        Id,
        StartDate,
        EndDate,
        Duration,
        DATEADD(MONTH, 1, MonthStart) AS MonthStart,
        EOMONTH(DATEADD(MONTH, 1, MonthStart)) AS MonthEnd
    FROM TaskMonths
    WHERE DATEADD(MONTH, 1, MonthStart) <= EOMONTH(EndDate)
),
WorkDayCounts AS (
    SELECT
        Id,
        MonthStart,
        -- 计算当前月份内任务覆盖的实际工作天数（排除周六周日）
        SUM(CASE WHEN DATEPART(WEEKDAY, dt) NOT IN (1,7) THEN 1 ELSE 0 END) AS MonthWorkDays,
        -- 计算任务总工作天数
        (SELECT SUM(CASE WHEN DATEPART(WEEKDAY, dt) NOT IN (1,7) THEN 1 ELSE 0 END) 
         FROM (SELECT DATEADD(DAY, n, StartDate) AS dt
               FROM (SELECT TOP (DATEDIFF(DAY, StartDate, EndDate)+1) 
                     ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS n
                     FROM master..spt_values) AS Numbers) AS TaskDays) AS TotalWorkDays
    FROM TaskMonths
    -- 生成当前月份内任务覆盖的所有日期
    CROSS APPLY (SELECT DATEADD(DAY, n, CASE WHEN MonthStart < StartDate THEN StartDate ELSE MonthStart END) AS dt
                 FROM (SELECT TOP (DATEDIFF(DAY, CASE WHEN MonthStart < StartDate THEN StartDate ELSE MonthStart END, 
                                            CASE WHEN MonthEnd > EndDate THEN EndDate ELSE MonthEnd END)+1) 
                       ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS n
                       FROM master..spt_values) AS MonthNumbers) AS MonthDates
    GROUP BY Id, MonthStart, StartDate, EndDate, Duration
)
SELECT
    Id,
    FORMAT(MonthStart, 'yyyy-MM') AS 月份,
    -- 按比例分配时长，保留两位小数
    ROUND(Duration * (CAST(MonthWorkDays AS FLOAT) / TotalWorkDays), 2) AS 对应时长
FROM WorkDayCounts
ORDER BY Id, MonthStart
OPTION (MAXRECURSION 0); -- 处理跨多个月的任务，放开递归限制

方案二：日期维度表（高频查询首选，效率更高） #

如果需要频繁执行这类查询，建议提前建立日期维度表（包含日期、是否工作日、所属月份等字段），并为相关字段建索引，这会大幅提升查询效率：

1. 创建日期维度表示例 #

CREATE TABLE DateDimension (
    DateKey DATE PRIMARY KEY,
    IsWorkDay BIT NOT NULL, -- 1=工作日，0=周末/节假日
    YearMonth VARCHAR(7) NOT NULL -- 格式：yyyy-MM
);

-- 填充数据（示例填充2020-2030年的日期）
WITH Dates AS (
    SELECT CAST('2020-01-01' AS DATE) AS DateKey
    UNION ALL
    SELECT DATEADD(DAY, 1, DateKey)
    FROM Dates
    WHERE DateKey < '2030-12-31'
)
INSERT INTO DateDimension (DateKey, IsWorkDay, YearMonth)
SELECT
    DateKey,
    CASE WHEN DATEPART(WEEKDAY, DateKey) IN (1,7) THEN 0 ELSE 1 END AS IsWorkDay,
    FORMAT(DateKey, 'yyyy-MM') AS YearMonth
FROM Dates
OPTION (MAXRECURSION 0);

-- 建索引优化查询
CREATE INDEX IX_DateDimension_YearMonth_IsWorkDay ON DateDimension(YearMonth, IsWorkDay);

2. 基于日期维度表的查询 #

WITH TaskWorkDays AS (
    SELECT
        t.Id,
        d.YearMonth AS 月份,
        SUM(d.IsWorkDay) AS MonthWorkDays,
        SUM(d.IsWorkDay) OVER(PARTITION BY t.Id) AS TotalWorkDays,
        t.Duration
    FROM YourTaskTable t
    JOIN DateDimension d 
        ON d.DateKey BETWEEN t.StartDate AND t.EndDate
    WHERE d.IsWorkDay = 1
    GROUP BY t.Id, d.YearMonth, t.Duration
)
SELECT
    Id,
    月份,
    ROUND(Duration * (CAST(MonthWorkDays AS FLOAT) / TotalWorkDays), 2) AS 对应时长
FROM TaskWorkDays
ORDER BY Id, 月份;

关键优化点说明 #

• 日期维度表优势：预先计算好工作日标记，查询时无需动态生成日期，结合索引能将复杂的日期计算转化为简单的JOIN和聚合，效率提升显著，适合高频使用场景。
• 递归CTE注意事项：使用OPTION (MAXRECURSION 0)解除默认递归次数限制，避免跨多年任务报错，但递归CTE的性能低于日期维度表，适合临时查询。
• 工作日扩展：如果需要排除法定节假日，只需在日期维度表的IsWorkDay字段中更新对应日期为0即可，无需修改查询逻辑。

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