多次操作下的日期差计算：SQL查询异常结果修正

The issue with your current query is that it's joining every "Filing Rejected" record for a Source ID with all subsequent "Filing Resubmitted" records, which is why you're getting that extra row where the 1/3/2023 rejection is paired with the 1/9/2023 resubmission. To fix this, we need to explicitly pair the nth rejection with the nth resubmission for each Source ID using window functions.

Step-by-Step Solution #

We'll use ROW_NUMBER() to assign a sequence number to each rejection and resubmission per Source ID, then join on both the Source ID and sequence number to get the correct 1:1 matches.

Here's the corrected query:

WITH Rejections AS (
    SELECT 
        Source_ID,
        Created_Date AS Rejected_Date,
        ROW_NUMBER() OVER (PARTITION BY Source_ID ORDER BY Created_Date) AS Seq
    FROM History
    WHERE 
        Action = 'Filing Rejected'
        AND Created_Date BETWEEN '2023-01-01 00:00:00.000' AND '2023-01-31 23:59:59.000'
),
Resubmissions AS (
    SELECT 
        Source_ID,
        Created_Date AS Resubmitted_Date,
        ROW_NUMBER() OVER (PARTITION BY Source_ID ORDER BY Created_Date) AS Seq
    FROM History
    WHERE 
        Action = 'Filing Resubmitted'
        AND Created_Date BETWEEN '2023-01-01 00:00:00.000' AND '2023-01-31 23:59:59.000'
)
SELECT 
    r.Source_ID,
    CONVERT(varchar, r.Rejected_Date, 101) AS 'Rejected Date',
    CONVERT(varchar, rs.Resubmitted_Date, 101) AS 'Resubmitted Date',
    DATEDIFF(HOUR, r.Rejected_Date, rs.Resubmitted_Date) / 24 AS Difference
FROM Rejections r
INNER JOIN Resubmissions rs 
    ON r.Source_ID = rs.Source_ID 
    AND r.Seq = rs.Seq
ORDER BY r.Rejected_Date ASC;

Explanation of Changes #

• CTEs for Rejections/Resubmissions: We split the data into two common table expressions (CTEs) to isolate rejections and resubmissions. For each, we use ROW_NUMBER() partitioned by Source_ID and ordered by Created_Date to assign a sequence number (e.g., first rejection for Source ID 1 gets Seq=1, second gets Seq=2, etc.).
• Matching by Sequence Number: Joining on both Source_ID and Seq ensures that only the first rejection pairs with the first resubmission, the second rejection with the second resubmission, and so on—eliminating the extra cross-matched rows.
• Cleaned Date Handling: We moved the date conversion to the final select (instead of using MIN() unnecessarily) since each Seq will only have one date per Source ID.

Why Your Original Query Failed #

Your original INNER JOIN only checked that the resubmission date was after the rejection date, but didn't enforce that it was the next corresponding resubmission. Grouping by h2.Created_Date also meant every possible rejection-resubmission pair was returned, including unintended ones.

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