SQL Server实现每周初未结案件历史数量统计需求

需求背景 #

基于两个现有查询，生成2024年1月1日至今所有ISO周的未结案案件数统计：

• 统计节点为对应周的周一00:00:00
• 案件一旦结案（有ClosedDate值），后续周不再计入

现有查询说明 #

Query1：获取目标周维度数据

从[firstsource]提取2024年1月1日至今的唯一年份、年月组合、年ISO周组合：

SELECT
DISTINCT YEAR(DAG_CODE) AS Jaar,
YEAR(DAG_CODE) * 100 + MONTH(DAG_CODE) AS Maand,
YEAR(DAG_CODE) * 100 + DATEPART(ISO_WEEK, DAG_CODE) AS Weeknr
FROM [firstsource]
WHERE DAG_CODE BETWEEN '2024-01-01 00:00:00' AND GETDATE()

Query2：获取案件基础数据

从[secondsource]提取2023年1月1日至今的案件信息，未结案（状态New/Running）时ClosedDate为NULL，已结案（Cancelled/Closed）时ClosedDate有值：

SELECT
CaseNumber,
CreatedDate,
ClosedDate,
Status
FROM [secondsource] 
WHERE Createddate >= '2023-01-01 00:00:00'

最终查询语句 #

WITH WeekDates AS (
    -- 扩展Query1，计算每个ISO周的周一0点起始日期
    SELECT 
        DISTINCT
        YEAR(DAG_CODE) AS Jaar,
        YEAR(DAG_CODE) * 100 + MONTH(DAG_CODE) AS Maand,
        YEAR(DAG_CODE) * 100 + DATEPART(ISO_WEEK, DAG_CODE) AS Weeknr,
        -- 适配ISO周的周一起始（SQL Server默认周日为一周第一天，需调整）
        DATEADD(DAY, 1 - DATEPART(WEEKDAY, DAG_CODE) + CASE WHEN DATEPART(WEEKDAY, DAG_CODE) = 1 THEN -6 ELSE 0 END, CAST(DAG_CODE AS DATE)) AS WeekStartMonday
    FROM [firstsource]
    WHERE DAG_CODE BETWEEN '2024-01-01 00:00:00' AND GETDATE()
),
CasePeriods AS (
    -- 预处理案件：未结案的设为未来日期，统一判断逻辑
    SELECT
        CaseNumber,
        CreatedDate,
        ISNULL(ClosedDate, GETDATE() + 1) AS EffectiveClosedDate
    FROM [secondsource]
    WHERE CreatedDate >= '2023-01-01 00:00:00'
)
SELECT
    wd.Jaar,
    wd.Maand,
    wd.Weeknr,
    COUNT(cp.CaseNumber) AS OpenCaseCount
FROM WeekDates wd
LEFT JOIN CasePeriods cp 
    ON cp.CreatedDate <= wd.WeekStartMonday
    AND cp.EffectiveClosedDate > wd.WeekStartMonday
GROUP BY wd.Jaar, wd.Maand, wd.Weeknr
ORDER BY wd.Weeknr;

逻辑拆解 #

1. WeekDates：在Query1基础上，计算每个ISO周对应的周一0点日期，解决SQL Server默认周起始与ISO周的差异问题。
2. CasePeriods：把未结案的ClosedDate替换为未来日期，这样可以用同一个条件判断：案件创建于周一起始之前，且结案日期在周一起始之后，即为该周未结案案件。
3. 关联统计：用左关联保证所有周都能返回结果，即使某周没有未结案案件也会显示0，最后按周排序输出。

示例验证（结合给出的样本数据） #

以202401周（周一为2024-01-01）为例：

• 案件1001：创建于2023-12-01，未结案，计入统计
• 案件1002：结案日期为2024-01-01 09:01:16，不满足“结案日期>周一起始”，不计入
• 最终该周未结案数为1

以202405周为例：

• 案件1001、1005未结案且创建于周一起始之前，计入统计
• 案件1003、1004已结案且结案日期早于周一起始，不计入
• 最终该周未结案数为2

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