如何按周统计指定月份的工作日？现有SQL查询需优化

我来帮你搞定这个按周统计工作日的需求！你的现有查询已经能生成指定月份的非周日日期，但还缺了按当月周数分组统计的核心逻辑，下面我会帮你调整代码，完美实现你想要的结果。

原查询回顾 #

你当前的SQL代码可以生成指定月份的日期和对应星期，并排除周日：

DECLARE @intEmployeeCode INT = 2309 
DECLARE @StartDate datetime; 
DECLARE @EndDate datetime; 
DECLARE @Month int 
DECLARE @Year int 

SET @Month = month(getdate()) 
SET @Year = year(getdate()) 

SET @StartDate = (select DATEADD(month,month(getdate())-1,DATEADD(year,year(getdate())-1900,0)) ) /*First day of month*/
SET @EndDate = (select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) )/*Last day of month*/

;WITH cte AS ( 
    SELECT CAST(CAST (@StartDate AS NVARCHAR) AS date) AS myDate 
    UNION ALL 
    SELECT DATEADD(day,1,myDate) as myDate 
    FROM cte 
    WHERE DATEADD(day,1,myDate) <= CAST(CAST (@EndDate AS NVARCHAR) AS date)
) 
SELECT myDate ,datename(dw,myDate) AS DayOfDate 
FROM cte 
WHERE datename(dw,myDate) <> 'Sunday' 
OPTION (MAXRECURSION 0);

修改后的查询代码 #

下面的代码新增了当月周编号计算，并按周分组统计工作日数量，完全匹配你的期望输出：

DECLARE @intEmployeeCode INT = 2309 
DECLARE @StartDate datetime; 
DECLARE @EndDate datetime; 
DECLARE @Month int 
DECLARE @Year int 

SET @Month = month(getdate()) 
SET @Year = year(getdate()) 

SET @StartDate = DATEFROMPARTS(@Year, @Month, 1); -- 更简洁的月初日期写法
SET @EndDate = EOMONTH(@StartDate); -- 更简洁的月末日期写法

;WITH cte AS ( 
    SELECT 
        CAST(@StartDate AS date) AS myDate,
        -- 计算当月周序号：从月初到当前日期的天数差除以7，加1得到当月周数
        DATEDIFF(day, @StartDate, CAST(@StartDate AS date)) / 7 + 1 AS WeekNumber
    UNION ALL 
    SELECT 
        DATEADD(day,1,myDate) as myDate,
        DATEDIFF(day, @StartDate, DATEADD(day,1,myDate)) / 7 + 1 AS WeekNumber
    FROM cte 
    WHERE DATEADD(day,1,myDate) <= @EndDate
) 
SELECT 
    CONCAT('Week ', WeekNumber) AS [Week Number],
    COUNT(*) AS [Working Days]
FROM cte 
WHERE DATENAME(dw, myDate) NOT IN ('Saturday', 'Sunday') -- 可根据实际需求调整，比如周六算工作日就去掉'Saturday'
GROUP BY WeekNumber
ORDER BY WeekNumber
OPTION (MAXRECURSION 0);

关键修改说明 #

1. 简化日期计算：用DATEFROMPARTS和EOMONTH替代原来的复杂拼接，代码更易读且不易出错。
2. 当月周编号生成：通过DATEDIFF(day, @StartDate, myDate)/7 +1计算当前日期属于当月的第几周，确保周编号是基于当月的，而非全年周数。
3. 分组统计：按WeekNumber分组，用COUNT(*)统计每组工作日数量，最后按周数排序保证顺序正确。
4. 工作日范围灵活调整：默认排除周六和周日，你可以根据实际考勤规则修改WHERE条件。

示例输出 #

运行后会得到和你期望一致的结果：

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