SSMS中月度/年度工作日统计及计划计算的技术求助

需求清单 #

• 当前月份、当月总工作日数、当前日期为当月第几个工作日
• 年度总工作日数、当前日期为当年第几个工作日
• 计算每日计划额：当月计划额 ÷ 当月总工作日
• 计算月度累计（MTD）计划额：每日计划额 × 当月累计工作日
• 计算年初至今（YTD）计划额：已完成月份计划额总和 + 当月累计计划额

原代码问题修正 #

原代码存在年度工作日统计错误（复用当月假期数）、无法展示多月份数据的问题，以下是完整修正后的SQL代码：

;WITH MonthList AS (
    -- 生成当年所有月份的起止日期、名称及编号
    SELECT 
        DATEADD(MONTH, n-1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AS StartOfMonth,
        DATEADD(DAY, -1, DATEADD(MONTH, n, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))) AS EndOfMonth,
        DATENAME(MONTH, DATEADD(MONTH, n-1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))) AS MonthName,
        MONTH(DATEADD(MONTH, n-1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))) AS MonthNumber
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) t(n)
),
Holidays AS (
    -- 维护当年假期列表，可按需扩展
    SELECT CONVERT(DATE, holiday) AS holiday 
    FROM (VALUES ('2023-01-01'),('2023-01-16'),('2023-02-20'),('2023-04-07'),('2023-05-29'),('2023-06-19'),('2023-07-04'),('2023-09-04'),('2023-11-23'),('2023-12-25')) t(holiday)
),
WorkdayCalculations AS (
    SELECT 
        ml.*,
        -- 计算当月总工作日：总天数 - 周末数 - 当月假期数
        (DATEDIFF(DD, ml.StartOfMonth, ml.EndOfMonth) + 1)
        - (DATEDIFF(WK, ml.StartOfMonth, ml.EndOfMonth) * 2)
        - CASE WHEN DATENAME(DW, ml.StartOfMonth) = 'Sunday' THEN 1 ELSE 0 END
        - CASE WHEN DATENAME(DW, ml.EndOfMonth) = 'Saturday' THEN 1 ELSE 0 END
        - ISNULL((SELECT COUNT(*) FROM Holidays h WHERE h.holiday BETWEEN ml.StartOfMonth AND ml.EndOfMonth), 0) AS TotalWorkdaysInMonth,
        -- 计算当月累计工作日：当前月算到当日，其他月算全月
        CASE 
            WHEN ml.MonthNumber = MONTH(GETDATE()) THEN 
                (DATEDIFF(DD, ml.StartOfMonth, GETDATE()) + 1)
                - (DATEDIFF(WK, ml.StartOfMonth, GETDATE()) * 2)
                - CASE WHEN DATENAME(DW, ml.StartOfMonth) = 'Sunday' THEN 1 ELSE 0 END
                - CASE WHEN DATENAME(DW, GETDATE()) = 'Saturday' THEN 1 ELSE 0 END
                - ISNULL((SELECT COUNT(*) FROM Holidays h WHERE h.holiday BETWEEN ml.StartOfMonth AND GETDATE()), 0)
            ELSE 
                (DATEDIFF(DD, ml.StartOfMonth, ml.EndOfMonth) + 1)
                - (DATEDIFF(WK, ml.StartOfMonth, ml.EndOfMonth) * 2)
                - CASE WHEN DATENAME(DW, ml.StartOfMonth) = 'Sunday' THEN 1 ELSE 0 END
                - CASE WHEN DATENAME(DW, ml.EndOfMonth) = 'Saturday' THEN 1 ELSE 0 END
                - ISNULL((SELECT COUNT(*) FROM Holidays h WHERE h.holiday BETWEEN ml.StartOfMonth AND ml.EndOfMonth), 0)
        END AS AccumulatedWorkdaysInMonth,
        -- 年度总工作日：当年所有月份工作日总和
        SUM(
            (DATEDIFF(DD, ml2.StartOfMonth, ml2.EndOfMonth) + 1)
            - (DATEDIFF(WK, ml2.StartOfMonth, ml2.EndOfMonth) * 2)
            - CASE WHEN DATENAME(DW, ml2.StartOfMonth) = 'Sunday' THEN 1 ELSE 0 END
            - CASE WHEN DATENAME(DW, ml2.EndOfMonth) = 'Saturday' THEN 1 ELSE 0 END
            - ISNULL((SELECT COUNT(*) FROM Holidays h WHERE h.holiday BETWEEN ml2.StartOfMonth AND ml2.EndOfMonth), 0)
        ) OVER () AS TotalWorkdaysInYear,
        -- 年度累计工作日：已完成月份工作日总和 + 当前月累计工作日
        SUM(
            CASE 
                WHEN ml2.MonthNumber < MONTH(GETDATE()) THEN 
                    (DATEDIFF(DD, ml2.StartOfMonth, ml2.EndOfMonth) + 1)
                    - (DATEDIFF(WK, ml2.StartOfMonth, ml2.EndOfMonth) * 2)
                    - CASE WHEN DATENAME(DW, ml2.StartOfMonth) = 'Sunday' THEN 1 ELSE 0 END
                    - CASE WHEN DATENAME(DW, ml2.EndOfMonth) = 'Saturday' THEN 1 ELSE 0 END
                    - ISNULL((SELECT COUNT(*) FROM Holidays h WHERE h.holiday BETWEEN ml2.StartOfMonth AND ml2.EndOfMonth), 0)
                WHEN ml2.MonthNumber = MONTH(GETDATE()) THEN 
                    (DATEDIFF(DD, ml2.StartOfMonth, GETDATE()) + 1)
                    - (DATEDIFF(WK, ml2.StartOfMonth, GETDATE()) * 2)
                    - CASE WHEN DATENAME(DW, ml2.StartOfMonth) = 'Sunday' THEN 1 ELSE 0 END
                    - CASE WHEN DATENAME(DW, GETDATE()) = 'Saturday' THEN 1 ELSE 0 END
                    - ISNULL((SELECT COUNT(*) FROM Holidays h WHERE h.holiday BETWEEN ml2.StartOfMonth AND GETDATE()), 0)
                ELSE 0
            END
        ) OVER () AS AccumulatedWorkdaysInYear
    FROM MonthList ml
    CROSS JOIN MonthList ml2
    GROUP BY ml.StartOfMonth, ml.EndOfMonth, ml.MonthName, ml.MonthNumber
),
PlanCalculations AS (
    SELECT 
        MonthName,
        MonthNumber,
        TotalWorkdaysInMonth,
        AccumulatedWorkdaysInMonth,
        TotalWorkdaysInYear,
        AccumulatedWorkdaysInYear,
        -- 每日计划额：假设当月计划额为68933300，可按需调整
        68933300.0 / TotalWorkdaysInMonth AS DailyPremiumPlan,
        -- 月度累计计划额
        (68933300.0 / TotalWorkdaysInMonth) * AccumulatedWorkdaysInMonth AS MTDPremiumPlan,
        -- 年初至今计划额
        SUM(
            CASE 
                WHEN MonthNumber < MONTH(GETDATE()) THEN 68933300.0
                WHEN MonthNumber = MONTH(GETDATE()) THEN (68933300.0 / TotalWorkdaysInMonth) * AccumulatedWorkdaysInMonth
                ELSE 0
            END
        ) OVER () AS YTDPremiumPlan
    FROM WorkdayCalculations
)
SELECT DISTINCT
    MonthName AS 当前月份,
    TotalWorkdaysInMonth AS 当月总工作日数,
    AccumulatedWorkdaysInMonth AS 当前日期为当月第几个工作日,
    TotalWorkdaysInYear AS 年度总工作日数,
    AccumulatedWorkdaysInYear AS 当前日期为当年第几个工作日,
    ROUND(DailyPremiumPlan, 2) AS 每日计划额,
    ROUND(MTDPremiumPlan, 2) AS 月度累计(MTD)计划额,
    ROUND(YTDPremiumPlan, 2) AS 年初至今(YTD)计划额
FROM PlanCalculations
ORDER BY MonthNumber;

代码关键说明 #

1. MonthList：生成当年12个月份的完整日期维度，支持多月份数据展示
2. Holidays：独立维护假期列表，便于后续更新调整
3. WorkdayCalculations：统一计算月度/年度的总工作日、累计工作日，修正原代码中年度统计的错误
4. PlanCalculations：基于工作日数据完成各类计划额的计算逻辑，最终输出格式化后的统计结果

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