SQL Server中基于动态工作日占比的计算列查询需求

嘿，这就帮你搞定这两种计算方案的SQL Server查询语句！每个方案我都会先讲清楚逻辑，再给你可直接复用的代码：

这个方案里，我们需要计算两个核心值：

• Y：当前月份不含周日的总工作日数（比如2018年1月是27天）
• X：截至date列对应日期的前一天、当月内不含周日的工作日数（比如2018年1月27日的昨日对应23天）
  最终计算列就是 Target_L * X / Y。

下面是具体的SQL语句，我用了一个固定数值列表生成当月的每一天，再通过iso_weekday筛选掉周日（这个判断不受系统DATEFIRST设置影响，更可靠）：

SELECT
    date,
    Month,
    Target_L,
    -- 计算X：截至昨日不含周日的工作日数
    (SELECT COUNT(*) 
     FROM (SELECT DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1)) AS day_date
           FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
                        (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
                        (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31)) AS nums(n)
           WHERE DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1)) <= DATEADD(day, -1, date)
             AND DATEPART(iso_weekday, DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1))) != 7) AS x_days) AS X,
    -- 计算Y：当月不含周日的总工作日数
    (SELECT COUNT(*) 
     FROM (SELECT DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1)) AS day_date
           FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
                        (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
                        (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31)) AS nums(n)
           WHERE DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1)) <= EOMONTH(Month)
             AND DATEPART(iso_weekday, DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1))) != 7) AS y_days) AS Y,
    -- 最终计算列（转换为DECIMAL避免整数除法）
    CAST(Target_L AS DECIMAL(18,2)) * 
    (SELECT COUNT(*) 
     FROM (SELECT DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1)) AS day_date
           FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
                        (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
                        (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31)) AS nums(n)
           WHERE DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1)) <= DATEADD(day, -1, date)
             AND DATEPART(iso_weekday, DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1))) != 7) AS x_days)
    /
    (SELECT COUNT(*) 
     FROM (SELECT DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1)) AS day_date
           FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
                        (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
                        (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31)) AS nums(n)
           WHERE DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1)) <= EOMONTH(Month)
             AND DATEPART(iso_weekday, DATEADD(day, n-1, DATEFROMPARTS(YEAR(Month), MONTH(Month), 1))) != 7) AS y_days) AS Calculated_Target
FROM YourTableName;

注意：把YourTableName替换成你的实际表名，Target_L是你的目标值列。

这个方案逻辑更简单，不需要筛选日期：

• Y：当前月份的总天数（比如2018年1月是31天）
• X：截至date列对应日期前一天的当月天数（比如2018年1月27日的昨日对应26天）
  最终计算列是 Target_L * X / Y。

SQL语句如下：

SELECT
    date,
    Month,
    Target_L,
    -- X：截至昨日的当月天数
    DAY(DATEADD(day, -1, date)) AS X,
    -- Y：当月总天数
    DAY(EOMONTH(Month)) AS Y,
    -- 最终计算列（转换为DECIMAL避免整数除法）
    CAST(Target_L AS DECIMAL(18,2)) * DAY(DATEADD(day, -1, date)) / DAY(EOMONTH(Month)) AS Calculated_Target
FROM YourTableName;

额外说明 #

如果你的Month列不是DATE类型（比如是字符串格式的'201801'），可以先转换成DATE类型再使用，比如把EOMONTH(Month)改成EOMONTH(CAST(Month + '01' AS DATE))，确保函数能正确识别月份。

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