如何扩展SQL查询实现多日期范围的多列周工时统计？

针对你需要按年度周生成动态列报表、同时支持补录数据重新统计的需求，提供两种可行方案：

一、静态指定周数的实现方式 #

如果报表需要固定统计某几个周（比如固定最近3周），直接用SUM(CASE...)的聚合方式生成对应列即可，每次补录数据后重新运行会自动更新结果：

-- 定义各周的日期范围变量
declare @wk1start datetime = '2022/7/4 00:00:00', @wk1end datetime = '2022/7/10 23:59:59'
declare @wk2start datetime = '2022/7/11 00:00:00', @wk2end datetime = '2022/7/17 23:59:59'
declare @wk3start datetime = '2022/7/18 00:00:00', @wk3end datetime = '2022/7/24 23:59:59'

select 
    location,
    name,
    -- 统计对应周的小时数，无数据则返回NULL
    cast(SUM(CASE WHEN date_of_service between @wk1start and @wk1end THEN hours END) as numeric(36,2)) as wk1_hours,
    cast(SUM(CASE WHEN date_of_service between @wk2start and @wk2end THEN hours END) as numeric(36,2)) as wk2_hours,
    cast(SUM(CASE WHEN date_of_service between @wk3start and @wk3end THEN hours END) as numeric(36,2)) as wk3_hours
from your_table  -- 替换为你的实际表名
where name is not null
group by location, name  -- 必须同时按location和name分组，否则会合并同一地点下的不同用户数据
order by location, name

需要新增周列时，只需添加对应的日期变量和SUM(CASE...)语句即可。

二、自动生成周列的动态SQL方案 #

如果需要自动识别年度内所有周并生成对应列（新增周数据时无需手动修改代码），可以用动态SQL实现，步骤如下：

-- 1. 指定要统计的年度
declare @target_year int = 2022

-- 2. 生成年度内所有周的日期范围和列名临时表
CREATE TABLE #Weeks (
    WeekNum int,
    WeekStart datetime,
    WeekEnd datetime,
    ColumnName varchar(20)
)

-- 插入年度内所有周的起始/结束日期（按周一至周日计算，可根据需求调整）
;WITH DateCTE AS (
    SELECT CAST(CAST(@target_year as varchar) + '/01/01' as datetime) AS DateVal
    UNION ALL
    SELECT DATEADD(day, 1, DateVal) FROM DateCTE WHERE DateVal < CAST(CAST(@target_year as varchar) + '/12/31' as datetime)
)
INSERT INTO #Weeks (WeekNum, WeekStart, WeekEnd, ColumnName)
SELECT DISTINCT
    DATEPART(week, DateVal) as WeekNum,
    DATEADD(day, 1 - DATEPART(weekday, DateVal), DateVal) as WeekStart,  -- 周起始（周一）
    DATEADD(day, 7 - DATEPART(weekday, DateVal), DateVal) as WeekEnd,    -- 周结束（周日）
    'wk' + CAST(DATEPART(week, DateVal) as varchar) + '_hours' as ColumnName
FROM DateCTE
OPTION (MAXRECURSION 0)

-- 3. 拼接动态SQL语句
declare @sql nvarchar(max)
declare @case_statements nvarchar(max)

-- 自动生成所有周的统计语句（SQL Server 2017+可用STRING_AGG，旧版本用FOR XML PATH拼接）
SELECT @case_statements = STRING_AGG(
    'CAST(SUM(CASE WHEN date_of_service BETWEEN ''' + CONVERT(varchar, WeekStart, 120) + ''' AND ''' + CONVERT(varchar, WeekEnd, 120) + ''' THEN hours END) AS numeric(36,2)) AS ' + ColumnName,
    ', '
) FROM #Weeks ORDER BY WeekNum

-- 组装完整查询SQL
SET @sql = '
SELECT 
    location,
    name,
    ' + @case_statements + '
FROM your_table
WHERE name IS NOT NULL
GROUP BY location, name
ORDER BY location, name'

-- 4. 执行动态SQL
EXEC sp_executesql @sql

-- 5. 清理临时表
DROP TABLE #Weeks

说明：

• 周的起始/结束逻辑可自定义（比如改为周日至周六），只需调整DATEADD的计算规则。
• 每次运行报表时，修改@target_year即可切换统计年度，新增的周会自动生成对应的列。
• 补录数据后重新运行，会重新计算所有周的小时数，确保结果为最新状态。

关键修正说明 #

原单周查询的GROUP BY location存在逻辑问题：因为需要保留name列，必须同时按location和name分组，否则会将同一地点下不同用户的小时数合并，导致结果错误。

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