如何用SQL While循环优化双周站点数据统计代码？

现有一段可正常运行的SQL代码，希望通过WHILE循环优化，实现从指定起始日期（首个双周周期的开始）按双周统计各站点案例数量，直到最近一个周日。原代码如下：

DECLARE @Startdate DATE SET @Startdate = '2022-03-14'

DECLARE @enddate DATE
SET @enddate =  (select DATEADD(DAY, DATEDIFF(DAY, 13, @Startdate )+13, +13))


Select SiteName
      ,COUNT ( CASE WHEN CallDate between @Startdate and @enddate THEN CaseID END) as 'Period 1'
      ,COUNT ( CASE WHEN CallDate between  DATEADD(DD,14,@Startdate) and DATEADD(DD, 14 ,@enddate) THEN CaseID END) as 'Period 2'
      ,COUNT ( CASE WHEN CallDate between  DATEADD(DD,28,@Startdate) and DATEADD(DD, 28 ,@enddate) THEN CaseID END) as 'Period 3'
      ,COUNT ( CASE WHEN CallDate between  DATEADD(DD,28,@Startdate) and DATEADD(DD, 28 ,@enddate) THEN CaseID END) as 'Period 4'

FROM [PathwaysDos_LIVE].[dbo].[vwCases]
where SiteTypeID = 5

group by SiteName

优化思路 #

原代码硬编码了4个周期，无法自动适配到最近周日的动态周期数。通过WHILE循环结合临时表存储中间结果，最后用动态PIVOT转换成原代码的列展示格式，实现全自动化统计。

优化后代码 #

-- 声明基础变量
DECLARE @StartDate DATE = '2022-03-14'
DECLARE @CurrentStart DATE = @StartDate
-- 计算最近的周日作为统计截止日期
DECLARE @LastSunday DATE = DATEADD(DAY, -(DATEPART(WEEKDAY, GETDATE()) - 1), GETDATE())

-- 创建临时表存储各周期统计数据
CREATE TABLE #PeriodStats (
    SiteName NVARCHAR(100),
    PeriodNumber INT,
    CaseCount INT
)

DECLARE @PeriodNumber INT = 1
-- 初始化第一个双周周期的结束日期（起始日+13天，共14天）
DECLARE @CurrentEnd DATE = DATEADD(DAY, 13, @CurrentStart)

-- WHILE循环遍历所有双周周期，直到超过最近周日
WHILE @CurrentStart <= @LastSunday
BEGIN
    -- 统计当前周期的站点案例数并插入临时表
    INSERT INTO #PeriodStats (SiteName, PeriodNumber, CaseCount)
    SELECT 
        SiteName,
        @PeriodNumber,
        COUNT(CaseID) AS CaseCount
    FROM [PathwaysDos_LIVE].[dbo].[vwCases]
    WHERE 
        SiteTypeID = 5
        AND CallDate BETWEEN @CurrentStart AND @CurrentEnd
    GROUP BY SiteName

    -- 切换到下一个双周周期
    SET @CurrentStart = DATEADD(DAY, 14, @CurrentStart)
    SET @CurrentEnd = DATEADD(DAY, 14, @CurrentEnd)
    SET @PeriodNumber = @PeriodNumber + 1
END

-- 动态生成PIVOT列，适配所有统计周期
DECLARE @PivotColumns NVARCHAR(MAX)
SELECT @PivotColumns = STRING_AGG(QUOTENAME('Period ' + CAST(PeriodNumber AS NVARCHAR(10))), ',')
FROM (SELECT DISTINCT PeriodNumber FROM #PeriodStats) t

-- 构建动态PIVOT查询，转成原代码的列展示格式
DECLARE @PivotQuery NVARCHAR(MAX) = N'
SELECT SiteName, ' + @PivotColumns + N'
FROM #PeriodStats
PIVOT (
    SUM(CaseCount)
    FOR PeriodNumber IN (' + REPLACE(@PivotColumns, 'Period ', '') + N')
) AS PivotTable'

-- 执行动态查询
EXEC sp_executesql @PivotQuery

-- 清理临时表
DROP TABLE #PeriodStats

优化说明 #

1. 动态周期遍历：通过WHILE循环自动遍历所有从起始日到最近周日的双周周期，无需手动添加周期列
2. 灵活的结果展示：用动态PIVOT将行统计结果转换成原代码的列格式，兼容任意数量的周期
3. 准确的截止日期：自动计算最近的周日作为统计终点，无需手动更新
4. 简化周期计算：每个双周周期固定为14天（起始日到起始日+13天），逻辑清晰易维护

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