SQL技术问询：统计Incoming任务前Claim类任务的有效最大次数

需求：统计Incoming任务创建前，Claim Creation或Claim Creation Followup任务的创建次数最大值；若同一claim同时包含两类任务，仅统计Claim Creation Followup的次数。示例中claimnumber=123在2024-03-28的Incoming任务前，有效最大次数为2。
已尝试使用窗口函数但未达预期，完成了任务分组但不知后续步骤。

示例数据创建代码 #

DECLARE @t TABLE
           (
               claimnumber int,
               taskname varchar(100),
               completedate datetime,
               tasknumber int
           )

INSERT INTO @t
VALUES (123, 'Claim Creation', '01/01/2024', 455),
       (123, 'Incoming', '01/05/2024', 367),
       (123, 'Claim Creation Followup', '02/01/2024', 566),
       (123, 'Incoming', '02/02/2024', 367),
       (123, 'Claim Creation', '03/24/2024', 455),
       (123, 'Claim Creation Followup', '03/25/2024', 566),
       (123, 'Claim Creation Followup', '03/26/2024', 566),
       (123, 'Incoming', '03/28/2024', 367),
       (224, 'Claim Creation Followup', '02/02/2024', 566),
       (224, 'Claim Creation Followup', '02/25/2024', 566),
       (224, 'Incoming', '02/26/2024', 367)
  
SELECT * 
INTO #test
FROM @t

示例数据输出 #

期望输出 #

已尝试的代码 #

DECLARE @CreationTaskNum INT = 455;
DECLARE @FollowUpTaskNum INT = 566;
DECLARE @IncomingTaskNum INT = 367;

WITH SequencedTasks AS
(
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY ClaimNumber ORDER BY CompleteDate) AS Seq
    FROM 
        #test
),
Gaps AS 
(
    SELECT
        *,
        CASE 
            WHEN (Seq = 1 OR (TaskNumber in (@CreationTaskNum, @FollowUpTaskNum)
                  AND LAG(TaskNumber) OVER (PARTITION BY ClaimNumber ORDER BY Seq)
                 NOT IN (@CreationTaskNum, @FollowUpTaskNum)
                 )) 
                THEN 1 
                ELSE 0  
        END AS NewGroup
    FROM 
        SequencedTasks
)    
SELECT *
FROM gaps

解决方案 #

以下是实现需求的完整SQL代码：

DECLARE @CreationTaskNum INT = 455;
DECLARE @FollowUpTaskNum INT = 566;
DECLARE @IncomingTaskNum INT = 367;

WITH SequencedTasks AS (
    SELECT 
        *,
        -- 标记每个任务所属的Incoming区间组
        SUM(CASE WHEN TaskNumber = @IncomingTaskNum THEN 1 ELSE 0 END) 
            OVER(PARTITION BY ClaimNumber ORDER BY CompleteDate) AS IncomingGroup
    FROM #test
),
TaskGroups AS (
    SELECT
        *,
        -- 统计组内Followup和Creation任务的次数
        COUNT(CASE WHEN TaskNumber = @FollowUpTaskNum THEN 1 END) 
            OVER(PARTITION BY ClaimNumber, IncomingGroup) AS FollowupCount,
        COUNT(CASE WHEN TaskNumber = @CreationTaskNum THEN 1 END) 
            OVER(PARTITION BY ClaimNumber, IncomingGroup) AS CreationCount
    FROM SequencedTasks
),
GroupMaxCounts AS (
    SELECT
        *,
        -- 优先取Followup的次数作为ClaimCount
        CASE WHEN FollowupCount > 0 THEN FollowupCount ELSE CreationCount END AS ClaimCount,
        -- 计算组内的最大有效次数
        MAX(CASE WHEN FollowupCount > 0 THEN FollowupCount ELSE CreationCount END)
            OVER(PARTITION BY ClaimNumber, IncomingGroup) AS GroupMax
    FROM TaskGroups
)
SELECT
    Claimnumber,
    TaskName,
    Completedate,
    Tasknumber,
    -- 仅在目标任务行显示ClaimCount
    CASE WHEN TaskNumber IN (@CreationTaskNum, @FollowUpTaskNum) THEN ClaimCount END AS ClaimCount,
    -- 仅在组内第一个有效任务行显示MaxCount
    CASE 
        WHEN TaskNumber IN (@CreationTaskNum, @FollowUpTaskNum) 
             AND ROW_NUMBER() OVER(PARTITION BY ClaimNumber, IncomingGroup, 
                                    CASE WHEN FollowupCount > 0 THEN @FollowUpTaskNum ELSE @CreationTaskNum END 
                                    ORDER BY CompleteDate) = 1
        THEN GroupMax 
    END AS MaxCount
FROM GroupMaxCounts
ORDER BY Claimnumber, Completedate;

代码说明 #

1. SequencedTasks：通过累加Incoming任务的数量，为每个任务划分所属的Incoming前置区间组。
2. TaskGroups：在每个区间组内，分别统计两类目标任务的次数。
3. GroupMaxCounts：按照规则确定每个组的有效统计次数（优先Followup），并计算组内最大值。
4. 最终查询：按照期望输出格式，仅在对应任务行显示ClaimCount和MaxCount。

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